I have a master Worksheet which collects data from workbooks I distribute to
others. When I get them back filled in, I put them in the same folder with
the master (totals) spreadsheet, and when I open the master spreadsheet it
picks up whatever data is currently in the folder. When I get all the
spreadsheets back the master is complete, and I can issue a report on the
collective data.
The master spreadsheet is set up very simply with a column for each
spreadsheet, and the same number of rows as in the component spreadsheets
(over 100 rows), which are all identical except for the number in their file
name (1 - 13). Each cell in a given column references a cell in the
respective spreadsheet for that column with the [workbook name]worksheet
name!cell reference. That all works fine.
I have two separate problems:
I am still finalizing the design of the master spreadhsheet and the
component spreadhseets. I make changes only to the master sheet and the first
component sheet (#1). And only after changes are complete do I create the
other 12 component sheets by doing a save as from the #1 sheet.
The first problem occurs when I make further changes to the #1 sheet and the
master and then delete the other 12 component sheets only to re-create them
when I have finished the changes. What I find is that, while each cell in
each row in a given column contains a reference to the spreadsheet for that
column (1 - 13), after I have deleted all the component spreadhsheets, the
workbook name in every cell in every column of the master spreadsheet has
automatically changed to the last worksheet I deleted, and I have to change
them all back.
Profuse apologies for this incredibly long description of the question, but
it seemed necessary.
I will stop here and not get into the associated issues before there has
been discussion on the first.
Thanks, if you have read through this entire question.
Eric