HELP: how to evaluate a filename when looking up another sheet?

[Ben Nunn]

I hope someone can help here.

Suppose I have a document called 'sheet1.xls'.

Now I have another document master.xls, from which I want to reference
certain cells in sheet1.xls - but next week it will be sheet2.xls, then
sheet3.xls and so on.

I would like to set aside one cell in master.xls to point to sheet(n).xls -
whichever is appropriate, so I can then apply a formula to the cells in
master.xls, e.g.


='[sheet1.xls]TABLE1'!$A$3
='[sheet1.xls]TABLE1'!$A$4

etc.

Except that I don't want to point to sheet1.xls, I want to evaluate the
string to point to a file based on the contents of the one cell where I tell
the rest of the sheet that it is sheet1.xls - so I don't have to update
every reference, every time the file we are pointing to changes.

Can this be done?!?

Many thanks in advance for any help.

Cheers

BTN

 

[Jerry W. Lewis]

=INDIRECT("'[sheet1.xls]TABLE1'!$A$3")
returns the same value as
='[sheet1.xls]TABLE1'!$A$3
but based on a character string that you can build piecemeal. For
instance, if sheet1.xls is in cell A1, then
=INDIRECT("'["&$A$1&"]TABLE1'!$A$3")
would look up the workbook, as you requested.

Jerry

 

[Robert Rosenberg]

Note that in Jerry's example, you must open the sheet#.xls before the data
will update.
________________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

=INDIRECT("'[sheet1.xls]TABLE1'!$A$3")
returns the same value as
='[sheet1.xls]TABLE1'!$A$3
but based on a character string that you can build piecemeal. For
instance, if sheet1.xls is in cell A1, then
=INDIRECT("'["&$A$1&"]TABLE1'!$A$3")
would look up the workbook, as you requested.

Jerry

I hope someone can help here.

Suppose I have a document called 'sheet1.xls'.

Now I have another document master.xls, from which I want to reference
certain cells in sheet1.xls - but next week it will be sheet2.xls, then
sheet3.xls and so on.

I would like to set aside one cell in master.xls to point to sheet(n).xls -
whichever is appropriate, so I can then apply a formula to the cells in
master.xls, e.g.


='[sheet1.xls]TABLE1'!$A$3
='[sheet1.xls]TABLE1'!$A$4

etc.

Except that I don't want to point to sheet1.xls, I want to evaluate the
string to point to a file based on the contents of the one cell where I tell
the rest of the sheet that it is sheet1.xls - so I don't have to update
every reference, every time the file we are pointing to changes.

Can this be done?!?

Many thanks in advance for any help.

Cheers

BTN

 

[Ben Nunn]

Unless I'm very much mistaken, it was Robert Rosenberg
([email protected]), in message (e-mail address removed)
who said:

Note that in Jerry's example, you must open the sheet#.xls before the
data will update.
________________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

Excellent, does the job perfectly.

Cheers guys.

BTN