Home 
Search 
Today's Posts 
#1




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




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




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




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) 