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

  • Thread starter Thread starter Ben Nunn
  • Start date Start date
B

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
 
=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
 
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

Jerry W. Lewis said:
=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

Ben said:
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
Click to expand...
Click to expand...
 
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
Click to expand...


Excellent, does the job perfectly.

Cheers guys.

BTN
 
Back
Top