evaluate a concatenate expression

R

Russell.Ivory

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
 
G

Glenn

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


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

Gord Dibben

Just be aware that both workbooks have to be open when using INDIRECT


Gord Dibben MS Excel MVP

Got it!

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




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

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

Similar Threads

concatenate 2
simple countif formula 2
Using a worksheet function to create an unusual cell reference pat 3
Look up or Match Function? Excuse my ignornace. This could be simple.Variable Upgrades on Furniture 1
Concatenate and Multiply 1
How can I reference a cell within a formula 3
How to use autofilter in excel with formula reference changing 1
Concatenate 2

Top