View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Glenn Glenn is offline
external usenet poster
 
Posts: 1,240
Default evaluate a concatenate expression

Russell.Ivory wrote:
I have a spreadsheet that uses a reference from another spreadsheet by the
expression

='[YTDreturnB.xls]Data Sheet'!$C$6

that I've entered into a cell, say cell B3. The reference from the
YTDreturnB.xls spreadsheet is a date.

Since I have several spreadsheets that I'd like to reference, depending on
my task at hand, I would like to make the spreadsheet referred to in my
formula in cell B3 a variable itself that would be referenced from say cell
B1.

For example, in cell B1 I could type in the name of a spreadsheet I want to
reference, and then the formula in cell B3 would use the contents of cell B1
to complete the expression. I'm thinking I need some sort of EVAL() function
to evaluate my expression. For example, I want to do something like

=EVAL(CONCATENATE("'[",B1,"]Data Sheet'!$C$6") )

where cell B1 contains the text YTDreturnB.xls

Any ideas would be most appreciated.

Russell



=INDIRECT("'["&B1&"]Data Sheet'!$C$6")