This procedure is workable in any XL version, if you're willing to go this
route.
You would have a column on your Summary sheet, where each row of the column
would return the contents of cell B6 from your individual "School #"
workbooks.
The formulas in this column will obtain the number of the "School #"
workbook from just dragging down the formula to copy as far as needed, and
return values from opened and/or closed WBs.
These formulas will also be ready to return data from as yet "uncreated"
WBs.
This would be easy if the Indirect() function would work on closed WBs, but
since it doesn't, this workaround might satisfy you.
Enter this formula in say D5:
="='c:\new project\[school "&ROW(A1)&".XLS]school summary'!$B$6"
NOTICE, that what's in the formula bar does *not* match what's displayed in
the cell.
Using the "fill handle", drag down to copy, say to D100.
During this copying, is the *only* time that you can automatically increment
the WB name, so a few extra rows shouldn't hurt.
Now, while all the rows are *still* selected from the "drag copy", right
click in the selection and choose "Copy".
Right click in A5, and choose "PasteSpecial".
Click on "Values", then <OK>, then <Esc>.
You now have a column of "Text" formulas, linked to existing and "not yet"
existing WBs.
Since they are "Text", you aren't getting any errors in the column from
"non-existant" links.
If you have an existing WB, "School 1", click in A5, hit <F2> then <Enter>,
and you'll see the data from B6 in the "School 1" WB.
You could now go down the column, converting the formulas of existing WBs to
"true" formulas with the <F2> <Enter> keystrokes.
If you have a large number of already existing WBs, you could convert them
in a single block by simply selecting those appropriate cells in ColumnA,
then:
<Edit> <Replace>
In the "Find What" box enter "=" (no quotes),
In the "ReplaceWith" box enter "=" (no quotes),
Then <ReplaceAll>
And you should have all your existing links displayed.
You could enter your SUM() formula in, say A4:
=SUM(A5:A100)
And have your totals returned without having any errors displayed, since
Sum() will by-pass any text, including the non-converted text formulas.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
RB: thanks again for your effort in helping me out. The format would
be:
'c:\new project\[school 1.xls]school summary'!$B$6
The WB is "School 1" (there will be many of these, called school 1,
school 2, school 3 etc); the WS is "school summary" (this will exist in
every workbook); and the top cell of the column of numbers to be summed
is B6. I am trying to get the master summary (for school districts) of
all of the individual "school summary" pages. If we can get this to
work than I would copy/paste the rest of the column.
