"Indirect" refs to other workbooks
 

The "Indirect" help in Excel 2000 seems to indicate that any other workbooks
that are referenced need to be open for the indirect function to work. Is
there a way to get around this?

Or perhaps there is an easier way. I have 5 employees who send weekly excel
reports to me. The reports are predictably named with the month and first
day of the week (e.g. bob01-01.xls, fred01-08.xls, etc). I have one workbook
that links to each external workbook and sums it all up.

Right now, I have to change my targets every week (ie replace "bob01-01" in
every function with "bob01-08", etc) Find/replace works, but it is not very
elegant.

I would much rather add 7 to my date (01-08 + 7) and then concatenate that
with "bob" and "sheet1" and "!a1" etc, to automate these changes.
Is "indirect" the best way to do this, or am I making it overly complex? If
I have to have every workbook open, by the end of the year that will be 260
workbooks!

Thanks

"Indirect" refs to other workbooks
 

See http://tinyurl.com/8t6n9

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"JRoberts" wrote in message
...
The "Indirect" help in Excel 2000 seems to indicate that any other
workbooks
that are referenced need to be open for the indirect function to work. Is
there a way to get around this?

Or perhaps there is an easier way. I have 5 employees who send weekly
excel
reports to me. The reports are predictably named with the month and first
day of the week (e.g. bob01-01.xls, fred01-08.xls, etc). I have one
workbook
that links to each external workbook and sums it all up.

Right now, I have to change my targets every week (ie replace "bob01-01"
in
every function with "bob01-08", etc) Find/replace works, but it is not
very
elegant.

I would much rather add 7 to my date (01-08 + 7) and then concatenate that
with "bob" and "sheet1" and "!a1" etc, to automate these changes.
Is "indirect" the best way to do this, or am I making it overly complex?
If
I have to have every workbook open, by the end of the year that will be
260
workbooks!

Thanks