Excel - Referencing Worksheets in separate Workbooks

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

If you have followed my elaborate statement of the problem, the obvious
solution would be to label the external workbook references as absolutes to
prevent Excel from changing them, just like absolute cell references (which
is really all they are, except that they are in a seaparate workbook).  To
label a cell column or row as absolute you precede it with a dollar sign '$';
Excel would not accept this when applied to a workbook name.

Hi,

First thing I would try is
1. close the Master leaving the revised Worksheet1 open
2. Choose File, Save As and click on one of the files you want to replace.
3. Click Save and respond Yes to replacing the old file.
4. Repeat this for each of the 12 files.
Don't delete the old files first, and do this with the Master closed.

A second approach if this doesn't work:
Why not take a different approach, instead of deleting each workbook.  Close
the master and open one of the 12 workbooks you intended to delete.  Display
is workbook and the modified workbook1 side by side.  Windows, Arrange, Tile.
Then drag the new sheets from the Workbook1 file while holding down the Ctrl
key and drop them in the Workbook2 file.  Ctrl Drag is a copy command.  Now
delete the the old sheets and rename the new sheets.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire

If I understand your problem ....

Master has references to Component1 ... Component 13.

You delete Component2 ... Component13, and then open Master and
Component1. With both sheets open, the references to Component1 DON'T
refer to the spreadsheet on disk, they refer to the open workbook
Component1. You saveComponent1, everything's fine. Then you saveas
Component2, and all those references in Master change to follow
Component2. Then you saveas Component3, and they all change again. When
you're done, they all refer to Component13.

So, ....

Just close Master with its correct links intact before editing
Component1 and doing all those saveas's.


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=27089

OK thanks to both of you.  I was so shocked that Excel changed the workbook
names I just wanted to lock them in place as absolutes, which I would do if I
knew how.  All your suggestions make sense, and I am sure I can work through
that.  Closing the master and saving over the component spreadsheets instead
of deleting them I think will be the best solution.

Interestingly, I just found that once I recreated all the ocmponent files
the workbook references all changed back to what they were supposed to be.
This is good, but I would love to take control away from Excel in this case.
I have a second problem when changing the formulas in the master that
reference the separate workbooks.  With 13 columns and  100 rows that is 1300
formulas, with 100 to each column.  I have to change the references if I make
a change to the component format, and alter the location of the cells I am
referencing on the separate workbooks.
What I find is that when I change a formula with a separate workbook
reference I am prompted for file location.  I am not changing the location,
and I can cancel that dialog by hitting the cancel button or just hitting
escape, however when I am changing 100s of formulas I want to use Replace
All, and that means hitting Esc for every cell being changed during what
should be a one touch procedure.  Further, there are times when it doesn't do
this, and it seems arbitrary to me.  It did this when the component
spreadsheets were deleted, but recreating them all didn't ichange the result.
I just went back again to try it and now I am not getting the prompt.
Again, I seek control over this process, which serves no purpose in the first
place since I am not posting a new location.  The arbitrariness of the
problem suggests there is a bug here, but I am still wondering whether there
is a way to prevent the prompt.
Thanks again for your help.
Eric

You could use INDIRECT, but INDIRECT doesn't work with closed workbooks.


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=27089

I was able to correct the original problem by following the two
recommendations from shg and ShaneDevenshire. With the master sheet closed I
Saved As #1 to each of the other component spreadhseets, overwriting instead
of deleting, and the filenames on the master sheet did not change.
Before doing that I did the same thing, but with the master sheet open and
the filenames did all change to the last one overwritten.  So the key to the
problem is to close the master sheet before deleting or overwriting the
component sheets referenced in the master.
Eric

Hi,

Thanks for the feedback!  Sounds like things are working better.  I'm not
sure if the 1000's of formulas problem is still an issue, but, and I've never
tried this, you might be able to put the Replace All command into code and
use the

Application.DisplayAlerts = False

command to suppress them.
You could record the Replace command in a separate workbook where you don't
have to deal with the popup.  Then you could modify it to do what you need
and add the line mentioned above before the replace command.  Again, I've
never tested this with the replace command so I don't know the results, its
just a thought.
--
Thanks,
Shane Devenshire