Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
evaluate a concatenate expression
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
evaluate a concatenate expression
Got it!
=INDIRECT("'["&$B$1&"]Data Sheet'!$C$6") "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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
evaluate a concatenate expression
Just be aware that both workbooks have to be open when using INDIRECT
Gord Dibben MS Excel MVP On Tue, 3 Nov 2009 14:26:06 -0800, Russell.Ivory wrote: Got it! =INDIRECT("'["&$B$1&"]Data Sheet'!$C$6") "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems CONCATENATE expression | Excel Worksheet Functions | |||
concatenate expression error | Excel Worksheet Functions | |||
value expression | Excel Worksheet Functions | |||
need help with expression | Excel Discussion (Misc queries) | |||
UDF to evaluate result of concatenate() with additional arg. | Excel Discussion (Misc queries) |