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

Cumulative formula for word table 0
Excel does not accept text format! 0
Need help calculating probabilities 7
Excel - Wich Function should I use? 1
Concatenate when pasting. 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Date diffrence in days, weeks and months. 1
VBA Coding Help for Beginner 0

Top