I know there is a way to do this! There HAS to be!

January 13, 2008 6:31 PM

MS Excel question: Creating a master spreadsheet that dynamically updates when information is updated from any one of six other spreadsheets. Possible?

I'm doing an inventory of three buildings, each with two floors. I've made six different speadsheets (one for each floor) to help me keep track of things. My boss wants weekly updates on a single, master spreadsheet.

I know I could just copy/paste all of the information into a speadsheet but I was wondering if there was a way to create a single spreadsheet that dynamically updated itself whenever I added information to any of the six other spreadsheets?

I'm using MS Excel 2002. Thanks.

posted by Diskeater to Computers & Internet (6 answers total)

Best answer: I think if you have both spreadsheets open, type "=" in the master spreadsheet, then click in a cell in the detail spreadsheet, it will give you what you want. I think. If you're e-mailing the master, make sure it works when the master is not on the same computer as the detail.

Alternatively, why not just use one spreadsheet with different tabs for the master and detail?
posted by Dec One at 6:36 PM on January 13, 2008

The syntax is [filename]TabName!Cell

So if the stuff you wanted to link was in a spreadsheet named detail.xls on a tab named Sheet1, the reference would be [detail.xls]Sheet1!$A$1

As Dec One states, you could have it do it for you automatically by opening both spreadsheets and using the click feature. You can see the status of associated files with the command. This also sounds like a job better suited for tabs within the same file instead of multiple spreadsheets, but maybe you have a reason not to do that.
posted by Lame_username at 6:56 PM on January 13, 2008

This is totally why spreadsheets are awesome. Unless you have a good reason not to, I suggest what Dec One recommends and combine your current spreadsheet into one that has seven different "worksheets" in it. You can copy/paste what you already have, just open a new spreadsheet, select Insert > Worksheet a few times and then copy it all. When you go to take the data from one to the other you can just use the standard cell naming conventions. which is basically like this =Sheet1!A1 so it's like a formula that says "go to the worksheet named Sheet! and go to cell A1" This page explains it a little. These people talk about how to do it from different files, but my suspicion is that there's another way within excel to do this that is less complicated.
posted by jessamyn at 7:03 PM on January 13, 2008

Seconding (thirding) one spreadsheet with many tabs.

Spreadsheets (and databases) grew up alongside tree-structured file systems, and effectively duplicate many file system functions. They interact with the file system in odd ways.

In your situation, it is slicker to use excel sheets rather that file-system files, so all the sheets know about one another. Just make the first sheet a grand summary and index.
posted by hexatron at 7:11 PM on January 13, 2008

Not a spreadsheet, but do you have Access? Sounds like it's time to upgrade from spreadsheets to a database. Actually, I think Access even has an Inventory database template. Once you get the hang of it, you'll be much happier!
posted by cdmwebs at 4:14 AM on January 14, 2008

Response by poster: I'm marking Dec One's answer as the best but all of these answers were very helpful. I'm now using one spreadsheet with separate worksheets.

Thanks everyone!
posted by Diskeater at 6:33 AM on January 14, 2008