Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Consolidating/adding Data From Several Spreadsheets.

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have 9-10 people who track their daily activities on separate spreadsheets and turn them in at the end of the month. The format is the same for each spreadsheet or at least started out the same although some may periodically add lines for notes/information. Basically, I need to consolidate the daily and monthly totals from each spreadsheet into a master spreadsheet that looks the same as the original. I am open to changing the format if necessary in order to facilitate the process. I am using 2003. I replied to a previous post initially with my question, here is the link: http://www.mrexcel.com/forum/newrepl...te=1&p=2570960.

Here is a sample spreadsheet:
Counts

sample removed per OP request, Private Information

Ideally I want to consolidate all the spreadsheets into a master with everyone's totals added together.


Similar Excel Video Tutorials

Helpful Excel Macros

Output the Name of the Current Excel Workbook Including Extension - UDF
- Free Excel UDF to display the name of the current Excel workbook, including the workbook extension. This function has n
Format Cells as a Scientific Number in Excel Number Formatting
- This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put
Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w

Similar Topics







Hi everyone I know I post alot of questions but this project is proving difficult and I think this time I can explain well enough what I need done that it might be successful.


The proper way to test this is to first open the "Sample 01.xls" file on the "Main" spreadsheet click elaborate after telling the sheet where the files are location. This creates two more files, one of which you will need. After you run the elaboration macro keep the "Sample 01.xls" file open and open "Picking Diffences 04-27-2009 Daily.xls" and "productivity results for area.xls"

Then run the macro store on "Picking Diffences 04-27-2009 Daily.xls".


read below for additional details.


Basically I have three spreadsheets that contain data and I need to pull that data off of the one and merge it into the other.

spreadsheet "productivity results for area.xls" contains 3 columns of information needed, that will be merged into "Picking Diffences 04-27-2009 Daily.xls" note that the date in the picking differences file changes very frequently and not always based on today's date this spreadsheet catches people cheating the system we use for tracking cases per hour pulled in the warehouse I work.

"Picking Diffences 04-27-2009 Daily.xls" initially only contains the actual cases pulled by the user calculated using all of each users order totals. In the end it will contain, cases confirmed, hours and the employees name all of which are taken from "productivity results for area.xls"

And "Sample 01.xls" this file creates "Picking Diffences 04-27-2009 Daily.xls" and also stores a spreadsheet named master which holds all the formulas and headers that are needed in the final version of "Picking Diffences 04-27-2009 Daily.xls"

I made a macro that just copied and pasted everything then deleted bunk information but it never worked the way I intended. I will include a sample file that will show what the file product will look like.

What needs to happen is (in order) copy all formulas from the "Sample 01.xls" file then paste them into "Picking Diffences 04-27-2009 Daily.xls", then copy the key ranges of data in "productivity results for area.xls" and paste them into "Picking Diffences 04-27-2009 Daily.xls" Then cleanup any temporary spreadsheets used if any were used. The file sheet should be in the "Picking Diffences 04-27-2009 Daily.xls" file.

The date used in the spreadsheet name is taken from cell B1 from "productivity results for area.xls" which is not in the correct format I realize as this report is exported.

I have attach what I have so far.

"Picking Diffences 04-27-2009 Daily.xls" has a macro stored on the workbook that can be run to see what I have so far.


I receive multiple spreadsheets each week as part of a survey. I need to
consolidate several of the fields onto one spreadsheet. How can I use
VBA/macro/function to consolidate the information from several spreadsheets
to one spreadsheet without having to open all the spreadsheets?
Thank you.



Ok, I am very very new so bear with me on this one. I have received several spreadsheets and would like to take the info from all of them and combine it into just one spreadsheet. Reason?: each spread sheet is the same format, but with different information. I want to be able to compare the data from same fields across diff spreadsheets and then if that data is the same to enter it onto the "master" spreadsheet. IF the data is not the same I would like it left blank so that I can see that I need to track down the right information IE: correct address.

Is there a certain process I should do? IE: combine the spread sheets and then convert that one master spreadsheet over to Access DB? My goal is to have one DB or spreadsheet that can be used. I would like to be able to querry the db maybe for all contracts expiering in the next qtr.... help any one??? or just ideas?


Hi,

I have 19 spreadsheets (all the same format). I need to summarise the totals of the different categories on these spreadsheets inot a master spreadsheet.

How do i go about creating links from the 19 spreadsheets that feed into the master?


Here is my problem (Using Excel 07):

I have 5 Excel workbooks with 50 spreadsheets each. Each book had a different random name and each spreadsheet in the workbook has a different random name(obviously). However, each spreadsheet has identical formatting and layout in every way. Here is what I need:

I need to take information from cells A8, B8, B4, F4, H4, K4, M4. I need the information from these cells in every spreadsheet in each workbook to be pulled into one 'master' summary spreadsheet with the information still being linked back to the spreadsheet from which it's been pulled, so if the information in the cell on the spreadsheet ever changes, it also changes on the 'master' spreadsheet. I need the information to be displayed in a table listed format, as goes:
____________________________A__ _B __C___ D__ _E ___F
(from Book1-Spreadsheet1): A8 | B8 | B4 | H4 | K4 | M4
(from Book1-Spreadsheet2): A8 | B8 | B4 | H4 | K4 | M4
...
(from Book1-Spreadsheet50): A8 | B8 | B4 | H4 | K4 | M4
(from Book2-Spreadsheet1): A8 | B8 | B4 | H4 | K4 | M4
(from Book2-Spreadsheet2): A8 | B8 | B4 | H4 | K4 | M4
...
(from Book2-Spreadsheet50): A8 | B8 | B4 | H4 | K4 | M4
(from Book3-Spreadsheet1): A8 | B8 | B4 | H4 | K4 | M4
(from Book3-Spreadsheet2): A8 | B8 | B4 | H4 | K4 | M4
...
(from Book3-Spreadsheet50): A8 | B8 | B4 | H4 | K4 | M4

And so on. I know I could do a =Book1-Spreadsheet1 type link to the master sheet I'm trying to create, but with 250 spreadsheets between 5 files, this would take forever.


Please let me know if you need any additional information.


Hello,

This is my problem. I have a master spreadsheet which contains 2 columns:

COLUMN A COLUMN B

Supplier 1 0.00
Supplier 2 0.00
Supplier 3 0.00
etc

Now, I receive several spreadsheets in the same format but containing values in Column B

How do I take the amounts from these spreadsheets, add them up (for each supplier) and paste the total in the master spreadsheet?

I have attached a sample of the master spreadsheet.

Many thanks in advance for any reply, assistance, code.


I have got a master spreadsheet that has links to other spreadsheets. These other spreadsheets are updated on a daily basis and the master spreadsheet updates.

What I don't understand is that the master spreadsheet only updates when I open the individual spreadsheets. It won't update automatically which is really frustrating. Can anyone tell me why this might be?

Thanks
Harry


I have been handed a new project where games that are identified for several portfolio's are all added to a master spreadsheet, along with some descriptive information, and a Y is placed in the column for each portfolio they will be added to.
I then have to go to the individual portfolio spreadsheets and add those same pieces of information. Additional information is added into columns to the right as work progresses.
I would love to find a way to have the game automatically added to the appropriate portfolio spreadsheet(s) when the entry is created in the master spreadsheet.
I tried setting a portfolio spreadsheet up with a query against the master spreadsheet, but there doesn't seem to be an option to preserve the additional columns of information that have been added to older titles.
Is there a simple way to do this? If not, is there a complex way that can be explained simply?


Hi-

I am trying to create a type of scrubbing workbook. For instance I am taking 3 separate spreadsheets (that represent shipped sales for parts) and merging it with the Master (payments confirmed) spreadsheet. I have colored the 3 spreadsheets different colors and left the master white. I need to somehow auto sum all three colors (with the matching part #) and compare it to the master (white) cells. If the totals do not match up, I need the white cell to turn Red. I have attached a sample. The actual spreadsheets are over 3k rows long each. So this is extremely tedious for me to do manually. Ughhh. HELP!! :0) I have attached a sample of what I am attempting to do.


Ok, I am very very new so bear with me on this one. I have received several spreadsheets and would like to take the info from all of them and combine it into just one spreadsheet. Reason?: each spread sheet is the same format, but with different information. I want to be able to compare the data from same fields across diff spreadsheets and then if that data is the same to enter it onto the "master" spreadsheet. IF the data is not the same I would like it left blank so that I can see that I need to track down the right information IE: correct address.

Is there a certain process I should do? IE: combine the spread sheets and then convert that one master spreadsheet over to Access DB? My goal is to have one DB or spreadsheet that can be used. I would like to be able to querry the db maybe for all contracts expiering in the next qtr.... help any one??? or just ideas?


Situation: I'm the production manager at a small manufacturing company. Over the years, 3 different excel database to track shop work orders have been started and maintained, (WO spreadsheet, the machine shop spreadsheet and the QC daily log spreadsheet). They each have different and relevant info but all have the WO number in common. They're each over 500 rows long. I'd like to combine/merge/consolidate/whatever them into one master spreadsheet but I'm hoping there's an easy way to do it without doing one line at a time.

Question: How do I combine them into one?

Thanks,
Dave


I am workind daily on master spreadsheets. I have seperate spreadsheets for
specific account types and one master that contains them all. The size for
some of the individuals is extremely large compared to the master which
contains all . Could there be some type of formatting error or some
information that is hidden within these spreadhsheets that would make them to
large to work with . Could there be a corruption within the excel that is on
my PC that may cause this size variance to occur?

I copy new spreadsheets into the master each week. Could copying the
information into the master cause the information to duplicate and enlarge
the size of the workbook?



I have 19 spreadsheets (all the same format). I need to summarise the totals of the different categories on these spreadsheets inot a master spreadsheet.

How do i go about creating links from the 19 spreadsheets that feed into the master?

the sheets i want get the info off are called "benefits realisation" and the actual workbooks are called "FY08-10 Planning Capex Initiative #1" etc etc to 19 ...

any ideas would be greatly appreciated!




I am creating a master spreadsheet that will be linking to spreadsheets that are created on a daily basis.

e.g. Each day a member of staff will save the same style spreadsheet but save it as staff 23.10.2009 an so on. I would like to know if it is possible to create a master spreadsheet that will grab data from all excel spreadheets that i save in one location.

So if i create a folder called staff and all the daily spreadsheets are saved in there would there be a way that a master spreadsheet could get data from all files?

Sorry if this is rambling and unclear but couldnt think of any way to say it

Thanks for your help


I want to have multiple spreadsheets, each where only certain individuals can modify. Then I want to have a master spreadsheet which only I can view/modify (I can take care of all the permissions with that), which pulls all the data from all the other individual spreadsheets and allows me to view the whole picture basically. My spreadsheet should be on one sheet and view all the others data. The other spreadsheets will be flat spreadsheets, no code or formulas. Can you help me figure out how to make my master spreadsheet? Thanks!

Chris


hi
there are a number of spreadsheets that i have and my task is to update any data added or changed on these spreadsheets, to a master spreadsheet. ive worked out that if data is changed, paste special -> paste link will do that, but im mainly concerned about how to add data automatically from the different spreadsheets, so that its is added to the master. i have been trying to use macros but havent managed to get there yet
example:

book1:
A B
1 kj ll
2 nj hu
3 mk nj
4 mf re

master:
A B
1 kj ll
2 nj hu
3 mk nj
4


what i would like to do is automatically copy row 4 into the master spreadsheet.

any help will be appreciated

reeceponse


I receive about 50 spreadsheets a week for a survey. I need to consolidate
the information into one spreadsheet. How can I crete a macro/formula/vba
script which will automate this process so that I don't have to open the 50
spreadsheets? Thank you.



Hi,
Can someboby help me?
Here is what happens. My master spreadsheet is very big: 123 rows and
7 columns. I need to divide the master spreadsheet into 8
separete (smaller) spreadsheets to send those smaller
spreadsheets to 8 different people. The problem is when I
try to first sort the master spreadsheet by peoples' name and then copy and
paste the sorted section into a brand new spreadsheet, Excell messes up the
format. I have been struggling spending numerous hours on just trying to
format that monster. The only solution I found is to save
the master spreadsheet 8 times and delete sections that
don't apply, which makes the process extremely time
consuming and prone to error. Is there a better way to
manage large spreadsheets like that? I tried the Help
section, but it did not seem to really apply to this issue.
Any help is greatly appreciated.
Thank you




Hi all,

Am wondering if any of you can help me at all.

I currently have 7 members of staff in my team who all publish vacancies on a daily basis.

A way off kepping track off their work load we created a master spreadsheet with all the details like team member name, job title, location, pay scale, number of hires etc.
We have had some problems with the data getting over typed recently.

Am wondering is there anyway that if every team member has their own spreadsheet i could pull the data from their spreadsheets to the master spreadsheet on a daily basis? That way the master spreadsheet is not getting touched by the team members

Thanks in advance


I am trying to consolidate some time sheet information and have got this far.

Person1, Person2 and Person3 have their own spreadsheet to enter their times so no person locks up the other persons as they would if it was all in one spreadsheet.

A "Summary" Spreadsheet has 3 sheets Person1, Person2, Person3 and each sheet uses msquery to grab the data from the respective source spreadsheet.

A "Report" spreadsheet uses msquery to consolidate the data in in the summary spreadsheet by saying "select * from Person1 union all select * from Person2 union all select * from Person3" from the "Summary" spreadsheet.

The summary spreadsheet is only in use because I have not been able to find out how to do the union directly from three spreadsheets.. it works fine from three worksheets in the same spreadsheet.

Will Excel and Msquery support a sql query that accesses three separate spreadsheets?

thanks in advance for any help.


I previously asked for help on linking spreadsheets. Here is my original post and the reply I received:


At work I have several spreadsheets for adding cheques eg 5 cheques for the Aquatic centre adds up to $100, 7 cheques for animal registration adds to $200 etc. The spreadsheet adds the number of cheques and totals automatically.

There is also a master spreadsheet to summarise all the totals. Instead of showing the value of every cheque for the Aquatic Centre, it just shows the number of cheques and the total, and so on,for all the cheque categories and a master total for all cheques.

After I finish entering all the categories of cheques in individual spreadsheets I enter them in for the master spreadsheet.

Please someone tell me if it is possible to link the individual spreadsheets in with the master spreadsheet so that the master spreadsheet is updated automatically as the individual spreadsheets are updated. And most importantly, tell me how to do this!

I am Australian so you will have to excuse my spelling of cheque.




--------------------------------------------------------------------------------

easy
on your master sheet in the cell for the total you want to show type = and
then click on the total cell from your other worksheet,and for the number of
cheques type= and click on the cel that shows the number of cheques.


I tried this advice at home and it worked. I tried it at work by creating lots of worksheets within the one worksheet. I did this by copying and pasting from the original spreadsheets because they are formatted with company logo etc and it would take quite a while to do them myself from scratch. I found that I was able to get the total number of cheques to be automatically updated in the master spreadsheet for each cheque category but not the dollar values. This seems strange. Whenever I tried a horozontal line appeared on the master spreadsheet where the dollar total should appear. I was not able to play around with the spreadsheets much because they are password protected. But for the dollar values the column that adds them has been merged so that the formula to sum is something like =SUM(A3:b7) while the column for adding the no. of cheques is a single column.

What are the possible reasons for the dollar values not automatically updating?

Is it becase the column is 2 cells wide? Or could it be to do with the password protection?

I will find out tomorrow if anyone know the password to unprotect the spreadsheet. Another option would be to just copy the cells that I need for formatting such as the company logo and start from scratch to get it working that way.


This is my first post, so I am sorry if this has already been covered, but will ask anyway. Maybe someone that lurks here frequently can point me in the right direction.

I have two spreadsheets. Each spreadsheet has roughly 65% overlap (same names)with the names of companies.

In addition, each spreadsheet has about 17 columns with 10 or 12 columns being the same data, i.e. company name, city, state, phone etc...

However, each spreadsheet also has several other columns that are unique to each.

I would like to create on MASTER spreadsheet which combines both companies and columns of information from both spreadsheets.

Can excel do this? Or, need I purchase an add-on program like "Consolidator" which I have seen advertised on the web?




Afternoon All,

I have a spreadsheet (master) which has many links to other spreadsheets (slave). The slave spreadsheet are all the same format and the master spreadsheet is an overview of the information stored in the slave sheets.

In the master spreadsheet I have a macro which adds the links to the slave sheet when a new row is added.

Here is the question:

My macro current hard codes in the links into the master sheet, for example:

='\\xxx\yy\CONFIDENTIAL\Live Projects\PHRs\[ 11957 =.xls]PHR'!H3

The red bits in the link are the same for all the rows of data, the only thing that changes is the 5 digit number.

Is there anyway to change the linking to something a bit slicker? In the macro there are about 50 lines of code just inserting the links!

I have had a look at the INDIRECT function but the slave sheets won't be open to update as there are about 50 individual sheets! Opening them all to update the info would crash my machine!!

So, sorry for the long post, I hope it makes sense!!

TIA,

MoonWeazel








I have 8 spreadsheets that I want to consolidate onto one "master" spreadsheet and they are all within the same workbook. They all have the same columns of information, just of different lengths. I know how to get one or two lines of info to transfer, just cannot get all the info to transfer when the individual spreadsheets are updated/ifo changed. Any assistance would be greatly appreciated.


HI I hope someone can help me with this, i am trying to consolidate multiple spreadsheets which are located in a folder, all the spradshets look the same and have the same column headers, just different data on them. So I would just like to copy the data not the column headers...

I found some code in a book to consolidate all open workbooks into one Master Workbook, this could work but I dont want to have all 180 spreadsheets open..I would like to just consolidate from the Folder....

Any ideas on how to do this?

thanks