Thread: creation of "master sheets" or "template"?

Hi all,
I'm trying to get something done on excel but i am not getting much further so i'm looking for you guys on how i can get this done.
First let me try and explain what i need.
I'm doing an experiment which will result in data 3 times a day for 7 days. The device i'm using can output the data to an excel xls file. Data is in the form of a table (A-H x 1-12) and in the end of each experiment (7 days long) i get a file with 21 sheets.
I've taken this file and created 3 more sheets:
-a description sheet, with some notes and schemes of the experiment
-a data&tables sheet with takes the data from sheets1-21 into new tables (some averaging is needed and there are controls, blanks, etc)
-a graphs sheets with takes the data from the data&tables sheet to create several charts

This is all working fine in the file i've just described.
However, the complete set of experiments results in approximatively 50 excel xls files and different persons will be doing the readings. Moreover, follow-up experiments may lead to even more data.
Ideally what i was trying to do was to create a file with only the first 3 sheets (master or template sheets as i call it) and get the device to add the new sheets to the already existing file. The problem is that when i delete sheets1-21 to create such file all the referents in the formulas to those sheets turn to "REF"....... so that doesn't seems to work. Is there a way to make excel ignore the missing sheets until the moment i "make it" do the calculations?
I tried to could copy the 3 first sheets to the new xls file created by the device but it maintains the references to the old file sheets. Moreover, as several people will be using the files i would like to keep editing to the minimum.
What's your take on this? Should i be doing this some other way, or looking at another application?

thanks in advance,
Carlos

Hi Carlos,

It sounds like you need to copy all the data using "Values Only" onto the single sheet where you then do you calculations. If your calculation sheet has formulas working on data from other sheets, you can't delete them without making your formulas corrupt.

I'd copy all the data collected to a single large worksheet and do calculations from it.