Linking a multiple spreadsheets to a master spreadsheet

[ccombs4569]

I have looked for the solution to this, but have had no luck. I need
to link multiple excel spreadsheets to a master spreadsheet, where
each sheet on the master would represent one external spreadsheet. If
the info on the external spreadsheets are changed, the master should
reflect those changes and visa versa. I am using Excel 2007. If
someone could point me in the right direction that would be great.
Thanks!

 

[Dave Peterson]

Excel doesn't work like this. It can pull the values from one cell to another
via a formula, but it can't easily push the data from one cell to another (no
matter where it's located).

You may be able to mess around with event macros, but I think you're setting
yourself up for frustration and disappointment.

I've found that if I can keep the data in one location--a single sheet in a
single workbook, it works better. I have more faith in the integrity of the
data and I can do many more things to that data.

If I need to create separate worksheets in separate workbooks based on one of
the fields in this single sheet, I'd make "report" worksheets--no changes
allowed in them (that are reflected in the original worksheet).

You may want to look at these:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

 

[ilia]

I'm inclined to agree with Dave. It would be tricky to pull this off,
since there's no way to actually "link" the sheets.

If you've got your mind set on this, however, here's an off-top-of-my-
head development strategy.

1. Create a list of linked workbooks on your master spreadsheet, with
full path.
2. Write some code that pulls up each of your multiple spreadsheets
and copy their worksheets into the master - one-time operation unless
something changes.
3. Each time you open the master workbook subsequently, have it check
the last date changed of the linked workbooks, and update as
necessary. If all you need is to view all the individual sheets in
the master workbook, then you're done.
4. Now you have two options: either update on each change in real-time
- this could be a problem. You can trap the SheetActivate event and
open the pertinent file, then watch for Change events. The second
option is to update on Deactivate event, tracking changes as you go.

Hope that helps.