Merging and Linking Worksheets

[zmenloans]

I'm fairly new to Excel and I have a question that I don't know if this even
works in Excel. Currently, I have five employees working on five different
workbooks all day. They have about forty columns (each workbook has the same
columns) and the records are continually added and removed. These workbooks
NEED to stay separate files.

What I want to do is create a new workbook that would have a sheet that
combines the five sheets. It would be a separate file from the other five,
but have one spreadsheet that is the combinate of all five and updates with
their updates. Since all of the columns are the same, I figured there was a
way to do this. The most important thing is that it will take all of the
information from one worksheet in each workbook and combine them to one
worksheet in the new workbook.

To make it clearer:
Five separate workbooks (called Balocs, AFS, Leases, BILs, and COLL). They
all have the same worksheet inside (called CurrentInventory).
I want a new workbook (called Total) that will grab all the records from the
five workbooks and put them in one consolidated worksheet (called
TotalInventory). A HUGE importance is that when one of the five workbooks is
updated, the total inventory is adjusted as well to add, edit, or delete the
record.

If you need more clarification, please let me know. I'm new on the
terminology so please just bear with me.

Thanks!

 

[Ron de Bruin]

Hi zmenloans

One way

When you want your data you can use this add-in to get the content from your workbooks
Use one folder with your 5 files
http://www.rondebruin.nl/merge.htm

 

[zmenloans]

Ron,
Thank you for getting back to me so quickly! I tried the program and it
definitley worked to merge everything, but I still have one question. Does
it update the merged sheet automatically from any change on other worksheets?
I tried to do that and I don't see the new entry.

Thanks

 

[Ron de Bruin]

I tried to do that and I don't see the new entry.

No

Run the add-in every time you want the data
This is easier to maintain then creating formulas.

Or do you have always the same amount of rows that you want to merge



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[zmenloans]

Each workbook might have different number of rows and changes daily. The
problem is that I won't be using the merged sheet...my boss will and he will
not want to run the add in each time. To make it clearer, my boss will take
the merged sheet, sort it, add formulas, etc. However, changing all of the
formulas and resorting each time would be too much of a hassel for him. He
would just like to open a merged sheet and have it update whenever another
record is added, edited, or deleted. Is this even possible?

Thanks so much for all your help thus far!

 

[Ron de Bruin]

Hi zmenloans

It is possible but not easy but maybe others have a good idea for you.
You can also post this in the public.excel.worksheet.functions newsgroup.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm