Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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.

View Answers     

Similar Excel Tutorials

Combine Data from Multiple Worksheets in Excel
The easiest way to combine and consolidate data in Excel. Simple method to combine data from multiple worksheets i ...
Print Only Specific Parts of a Worksheet in Excel
In Excel you can select parts of a worksheet to print while ignoring all of the other data on the worksheet. This a ...
How to Add, Remove, and Rearrange Columns and Rows in Excel
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...
Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...

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







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?


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


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 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




Hey guys,

Unfortunately I cannot attach a file because I would get fired for putting information on the internet. In short, I'm trying to auto sort data into spreadsheets based off of the month the project was started. We have a "Master" spreadsheet with all the projects, dates started, and other information which is irrelevant here. I would like the projects and all the data to get sorted into the correct spreadsheet via month. So we have the master sheet, a sheet for january, february, march, etc. For example, if project A was started in January, B in february, C in march; this data would get sorted into the correct monthly spreadsheet (the master sheet will be continuously added to daily). I thought I could figure this out with a vlookup but soon realized I had nothing on my monthly sheets as a lookup value. Any ideas at this point would be great!

-Dan

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.





I work in an agency where I need to produce monthly reports for over 40 clients. I need to automate as much as possible !

I have an aged creditors report which covers all my 40 clients with over 1,500 lines of aged invoicing spilt between the clients. This master sheet is run off once a month.

What I am looking to do is to is automate my month end process. I am setting up over 40 spreadsheets, one for each client with a sheet for each month for aged creditors.

What I am looking to do is each month, once I have uploaded my data into the master spreadsheet (sheet for each month) that all other spreadsheets will automatically pick up the data for that month. I have looked at vLookup which wasn't much use, have tried Array which didn't seem to do the trick. Has anyone got any suggestions?

Basically say for Client A, I just want to pick up all lines from my master spreadsheet relating to Client A, the client referred to in the first column of the master spreadsheet. I want to be able to copy the formula across each client so each month the info is imported automatically imported. If someone could point me in the right direction, I would be extremely grateful.

Many thanks,

Gary

Aged cred.jpg

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?


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


I was asked to combine 7 diffrent spreadsheets into one Master list. Seems simple enough, though there are specific actions that I need the Master list to do, that I just cannot figure out. what needs to happen is, if someone updates or Adds a new row of information into their spreadsheet, I need the Master list when opened to automatically update the new information, and/or Add in the new Row. ie. If I have 7 sheets with 10 Rows, each 1-10, I should have a total of 70 rows, and if someone adds a new row to their sheet, I will now have 71 rows showing, or if two people add 2 Rows of new information each, than it would re populate the list to show 74 Rows.

Is this possible? is there a way, to have excel distinguish when someone enters information into a blank row in a separate spreadsheet and automatically insert it into the Master list? and have the Master List distinguish that each spreadsheet has different information on the same Row, but still shows each different Row, and each new added Row from the sub spreadsheets on it?

Spsht 1 Row 1
Spsht 2 Row 1
Spsht 3 Row 1
Spsht 1 Row 2
Spsht 2 Row 2etc...

I hope this makes some sort of sense. Please help, anyone... if you can. It is greatly appreciated. Even if its as simple as a No, it cannot be done.


Hi all,

Im having a bit of blank day and have been staring at my screen for quite a while trying to think of some way to do this without it being to messy.

Anyway I need to setup a spreadsheet which does the following.....

I have a list of 5 jobs which are done every hour during the working day and a need a stat for how many was done at each hour. There are 4 people who do these jobs.

I need stats for - totals for each job, each day by each person.
- Overal total for each job each day
- Running totals of all stats during the month

Any ideas of how to lay to this out? We currently have a seperate sheet each day of the month in monthly spreadsheets but they get quite messy and doesn't look to great. I just need something to get me started. What sheets to have and how best to do it...

Any help on this would be great, its hard to explain on forum but thought I would give it a shot...

thank you

Matt


I would like to consolidate multiple spreadsheets within a specific folder on
a server to a single sheet on a spreadsheet located on my (or someone else's)
PC. The spreadsheets on the server are all in the same format, in that they
have identical columns as does the consolidation spreadsheet held locally.
Ideally I'd like the code to write to the next available line of the
consolidation spreadsheet. Thus.

Server

Spreadsheet1 - 3 rows of data
Spreadsheet2 - 4 rows of data
Spreadsheet3 - 6 rows of data
etc
etc

Local PC

Consolidation Spreadsheet - First row includes Headers.

So the Consolidation spreadsheet would take data from Spreadsheet1 and place
it on row 2,3,4 then place the data from Spreadsheet2 on row 5,6,7,8 etc.

Many thanks in advance for any help you can provide.




How can I put this. I have a Master Spreadsheet (a) with all the orders I process daily for customers. I have spreadsheet (b) were I add this information to be able to send to my directors. What I want to do is as I add the orders on spreasheet (b) I want the master spreadsheet (a) to be automatically updated with this new information being added on spreadsheet (b). How can I do this?? HELP!


I have two spreadsheets with multiple worksheets in them(A is the master and B is the copy). B was given out to some people at work to update certain information and then it was sent back with updated information in some of the rows. I have to go through and compare the information in B and make the respective changes in the master spreadsheet A. Is there a easier way then just comparing the two eye to eye and making the changes?


I'm exploring options to keep track of multiple employees on the same computer network working at various locations using a shared master excel spreadsheet that will update periodically by accessing timecard spreadsheets on employees computers. This maybe an impossible project, but it would seem possible with the limited knowledge I have of shared worksheets.

Ideally, a timecard spreadsheet would be kept on the employees computer. It would be password protected so only the employee could access the spreadsheet. It would be referenced in the master spreadsheet and periodically the master spreadsheet would poll the employee timecard spreadsheet to gather the time data necessary for payroll processing and management purposes.

The timecard spreadsheet would capture the time the employee makes the entry reflecting his/her in and out times in the spreadsheet. Employees would be instructed to record their times in and out as a first priority and the entry capture time would be compared and reported in the master spreadsheet. Significant differences between the time reported in and the data capture time would be reported in the master spreadsheet and reviewed by management for inconsistencies.

This would bring consistency to the time recording process for our organization and I would imagine it would have a broad application across many businesses. We are presently having employees write down their time and turn it in to their supervisors on paper submitted one time monthly...which is a very poor way to handle such a sensitive and critical aspect of our organizations resources.

Thanks in advance for your helpful comments!!!

NJ Will


Hi,

I am trying to find an effective way to paste all the data input from many spreadsheets into one master sheet. I sent out one spreadsheet with the same format to about 15 to 20 groups. The end result is that each user will send me back his/her data input in the same spreadsheet format. The spreadsheet contains a lot of information in the each row with over 30 columns. I was thinking to use Vlookup to find the exacty match to fill in the data into the master sheet; however, I don't want to link spreadsheet either. Can anyone suggest any tips to do this in a fast and easy way other than manually copying and pasting?

Thanks a lot,

Howdy




I am working on a payroll workbook in Excel 2010. I currently have it set up with 4 spreadsheets within the workbook. The first spreadsheet is a master list of all employees. Included in this spreadsheet is their name, pay rate, and pay source (whether they are paid through the department or through federal grants.

The second spreadsheet is the master payroll. I set up column 1 to automatically populate with the names from column 1 of spreadsheet 1. I used the following formula: =IF('Master Employee List'!A4="","",'Master Employee List'!A4). That formula continues down the column. Then, I manually type in the number of hours that they worked in the second column, and have a formula in the 3rd column which multiples those hours by the pay rate listed in the master spreadsheet. This continues for each pay period for the year.

The 3rd spreadsheet includes the same information from spreadsheet 2, but only includes department funded employees.

The 4th spreadsheet includes the same information from spreadsheet 2, but only includes federal funded employees.


The problem that I've encountered is: If I add a new employee into the master spreadsheet, once I sort that data, the information that I entered in spreadsheets 2, 3, and 4 are now all off by 1 row after that new employee's row. For example: If I add Abby Adams into the master spreadsheet and sort the data, when I go into spreadsheet #2, her name is listed at the top, but she has payroll information that used to be attributed to person that used to be first, but is now second on the list. Each employees information is now off by 1 row.

Is there a way so that the data that I'm manually entering in spreadsheet 2 always stays with that particular employee, even if another employee is added and sorted? Perhaps I just need to setup my spreadsheets differently so that I manually enter employee names on each spreadsheet.

I'm trying to automate the collection of data from various spreadsheets into one master tracking spreadsheet. There will be ~40 different "source data" spreadsheets (let's call them AA, AB, AC,...). I'd like to copy the entire contents of Sheet1 from each of these into the master tracker and name the newly created worksheet according to what is in cell Z1 for each source data spreadsheet.

For example, assume the souce data spreadsheets are as follows:
AA "John Doe" in Z1 on Sheet1
AB "Mary Jane" in Z1 on Sheet1
AC "Susie Derkins" in Z1 on Sheet1
etc...

The master tracking spreadsheet would have the following worksheets when finished:
John Doe
Mary Jane
Susie Derkins
etc...

and each of these worksheets would be a copy of what was in Sheet1 of the original source data spreadsheet.

Ideally, I'd like to open each of the source data workbooks in the background if possible so I don't have to open all of them individually one at a time to pull the data in. All of the source data spreadsheets will be in a folder so they could be looped through somehow I suppose?

Thanks for any help!
EB

I have several worksheets that I use to total up the prior day's business - sales, payouts, etc. I generate a new sheet every day and it is named by DOW and date, both the tab and at least one cell.

I also have several monthly sheets that I use to track certain information from the daily sheet - sales of a particular item or to compare daily sales totals. These sheets are generated every month and have a row for every day.

The 2 sheets are in different workbooks

I would like to automate my process so that when the macro runs to make the new daily sheet it sends the data from the daily sheet to the monthly sheet. I've had some success transfering from the daily to the monthly sheets within the same month but I'm at a loss as to how to grab the data when a new monthly sheet gets made without having to manually re referance stuff.

The sheets I uploaded show a sheet from each workbook and show a daily and monthly sample file to show what I'm trying to do.


I want to create two master spreadsheets and link certain fields in both on a third master spreadsheet that will summarize data.

I want users to be able to copy these spreadsheets and rename them and maintain the links. Is there anyway to do this?

Thanks,

Elvira Loredo


Hello,
I am at wits end and I hope you can help me with this problem i am facing.

I have multiple spreadsheets in which i need to gather fixed information from each one and paste (Transpose) the information to a master spreadsheet.
They all have the same layout and data within these spreadsheets are within the same fields as each other.

The Cell with data that are required from the multiple spreadsheets are;

* The Address Name in Cell 'C2'
* The Address Number in Cell 'C3'
* Scores - Cell Range = V10 to V12, V15 to V23, V27 to V30, V34 to V37, V41 TO V44, V47 TO V49 & V52 TO V56. (Scores range from 0 to 10,000)

Currently I have been copying this data from the 200 (multiple) spreadsheets and pasting (Transpose) this info to a master spreadsheet,
so i would then have the respective 200 row of information.

I have tried to look up similar questions online, but they offer no relation to what i need to do. So therefore I
hope you can be of some assistance which would be very grateful.

Kind Regards


Hi there

I am currently working on a spreadsheet which conatins formulas that link to numerous other spreadsheets.

The problem I am having is that when you open the new spreasdheet, in the cells which contain formulas linking to the other spreadsheets they display #VALUE! unless you actually open the source spreadsheets.

I want the formulas in the cells to update without the user having to open the source spreadsheet, otherwise there will be no point in having the new spreadsheet! I don't want the users to have to open numerous spreadsheets to view the information they need.

One thing I have noticed is that cells that link to another spreadsheet (but do not incorporate formulas) are working fine, although you do have to enter the password needed to access that particular source spreadsheet.

Can anyone help with this?

Thanks!