"Indirect" refs to other workbooks

J

JRoberts

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top