Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Consolidating Multiple Spreadsheets

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

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


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 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
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
- This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
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

Similar Topics







Hi

I am trying to view two seperate spreadsheets to compare pricing information I've compiled.

Both are saved as individual spreadsheets (not worksheets on the same spreadsheet). Typically, I right click on my PC taskbar and "tile windows vertically" so that I can simultaneously view multiple items.

However, with Excel, tiling doesn't work and I have to click in and out of each spreadsheet.

I know there must be a way to do this but I can't find the answer.
Thanks in advance!

p.s. I'm using Excel 2003


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


Hi all!
Thanks for your help in advance,

I have a small problem with a lookup I am doing currently.
I have 3 spreadsheets. 1 is the master which pulls it all together and the other 2 are spreadsheets provided by colleagues.
I have a list of items on my spreadsheet and need to pull some dates from the other spreadsheets from specific columns. However it is not as simple as a VLookup as the item list in the other 2 spreadsheets are not going to be necessarily in the same order as mine.

so is there anyway that there is a lookup way of searching for the item name in my master sheet, then looking in the other spreadsheet for the item name, then fetching the date given in that spreadsheet on that items row then inserting that value back into my master first spreadsheet where it should?

Is this a simple operation that can be done by function or is it going to require macro - the data will range to over 250 rows notably + I will be able to supply the data with regards column and row numbers hopefully in a few days - just a case of is it possible at this stage?!

Many thanks as always!


Hi all!
Thanks for your help in advance,

I have a small problem with a lookup I am doing currently.
I have 3 spreadsheets. 1 is the master which pulls it all together and the other 2 are spreadsheets provided by colleagues.
I have a list of items on my spreadsheet and need to pull some dates from the other spreadsheets from specific columns. However it is not as simple as a VLookup as the item list in the other 2 spreadsheets are not going to be necessarily in the same order as mine.

so is there anyway that there is a lookup way of searching for the item name in my master sheet, then looking in the other spreadsheet for the item name, then fetching the date given in that spreadsheet on that items row then inserting that value back into my master first spreadsheet where it should?

Is this a simple operation that can be done by function or is it going to require macro - the data will range to over 250 rows notably + I will be able to supply the data with regards column and row numbers hopefully in a few days - just a case of is it possible at this stage?!

Many thanks as always!


Hi

I have a master spreadsheet which contains a column headed names containing a list of names and two column headings, Telephone Number and Mobile Number which do not contain data. I then have a large number of spreadsheets which contains the same three column headings with data under each one. I am quite new to this and want to be able to automate the process so that the master spreadsheet with names and headings looks at the other spreadsheets in turn and if there is a match by name, it then pulls the data from the appropriate heading eg Tel. No, Mobile number. The end result will be that the master spreadsheet contains not only names, but telephone and mobile nos where there is a match. It would also be helpful to highlight any entries in the individual spreadsheet which do not match (beacuse they are not in the master spreadsheet) so that they could be manually added.

Hope this makes sense and would be a grateful for some help on the best way to make this work as I am sure it is quite straightforward for you experts!

Thanks in advance.

Chris


I am needing to know how (or if) I can link spreadsheets together from multiple users. What I am trying to do is to have individual spreadsheets on each of the people's pc's in the office and have one central spreadsheet on our server. I would like for the central spreadsheet to be linked to all the individual spreadsheets and be updated when the individual spreadsheets change.


Hi

I have 6 identical spreadsheets which need to be linked to one master spreadsheet.

How do I do this. Do I need to have all seven spreadsheets open at once?

The 6 spreadsheets will be simply added together.

Thanks for any help on this.

Stuart


I have a worksheet with colors and materials. Colors make up the colums and material make up the rows with sales data in the cels. I am trying to add all of my colors and material together on one spreadsheet.

I have 5 spreadsheets with different colors and materials and want to make one master spreadsheet with totals of all 5 spreadsheets.

For example I have the color red in leather and on 3 of the 5 spreadsheets I have sales of 10,15,13 so I would want the total sheet to go and look at all 5 spreadsheets and give me a total of 38 for red leather, any idea on the formula? I tried an index match, but it became too long and cumbersome.

Thanks


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.


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?


Hey all,

I am very new to making Excel do these fantastically helpful things.

I have a team of 5 or 6 with a copy of a spreadsheet and the master on a sharepoint. I am wishing to know how I go about getting the individual spreadsheets to update to the master spreadsheet preferably with a 'click of the button'

If someone can walk me through this I would be much appreciated.

Thanks in advance, DJ


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?


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



Hi,

Forgive me if this is an easy question, but i am new to VB and creating macros in excel, so i was hoping someone could help me.

Heres what i am trying to do.....
I am creating a master spreadsheet, which links off to maybe 100 other spreadsheets located in a filing system.

At the top of this spreadsheet i want to create a 'high priority' section, where this will take a priority number from the child spreadsheets (the priority number will be entered manually into a cell within the child spreadsheet), and the master spreadsheet will make a list of all the spreadsheets which contain priority 1 (and link off to them). The child spreadsheet will contain a macro/button which will update the master spreadsheet with its new priorty level.

Does this make sense? I would appreciate any help/direction anyone can give on this.
Taking it one step at a time, the first thing i would like to look at is how to get the master spreadsheet dynamically creating a list of the high profile child spreadsheets.

Thanks in advance to anyone who can help,

Rich


Hi there

I was wondering if anyone could help with this query.

Basically I have about 10 open spreadsheets (the name of these changes each time but has a certain format). What I need to do is write some code to cycle through all the open spreadsheets and copy and paste the data into a single master spreadsheet. All these spreadsheets have a single worksheet.

How can I go about doing this?

Many thanks in advance

David


Hello
I have a number of spreadsheets which are all stored in the same folder on a network, one of which is a Master which has links to all the other spreadsheets using the "Paste Link" function. The problem I have is that when I view the master a number of cells only display a portion of the linked data. Has anyone else come across this problem and if so do you have a solution.

Look forward to hearing from anyone who may be able to help.

Kind regards

TK_Hussar


I have a spreadsheet that is linked to many other spreadsheets. All these 'other' spreadsheets have different passwords to one another.

How can I update my spreadsheet from all of the 'other' spreadsheets without having to MANUALLY enter all of the passwords of the 'other' spreadsheets?

I presume there is a script/macro/VBA that can be created with all the passwords listed against their relevent spreadsheet, but I just don't know how do it. Help please!!

Please note that if I go to 'Update Values' for all of the 'other' spreadsheets then Excel ask me for a password for each and everyone, which is extremely labourious.


Hi,

I have a bit of a complicated problem. I want to create a macro that goes into about 25 folders each day, sees if there is a new spreadsheet (that could have a slightly different name each time) and then goes into that spreadsheet and pulls out five different columns, puts that in a new master spreadsheet, and then closes it. (So now I would have the information from 25 different spreadsheets all in one master sheet) Is this even possible?

Thank you.


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


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


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!


I have 11 spreadsheets where I need to combine them into one sheet. Basically, I want each individual spreadsheet to be a tab on a consolidated spreadsheet. Is there an easy way to do this, other than manually copying?


Hello Friends

Is it possible to have a code for combining multiple spreadsheets into one with spreadsheets' name printed besides the data combined? Besides this another code is required to put the data back in separate spreadsheets.

I am working on a project in which I need to combine the data, modify the data and then put it back in separate spreadsheets.

Thanks for your time

Rajesh


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.