If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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 
Ads 
#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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Problems CONCATENATE expression  tech1NJ  Excel Worksheet Functions  1  April 9th 09 05:45 PM 
concatenate expression error  philr  Excel Worksheet Functions  2  July 26th 07 04:18 PM 
value expression  RayB  Excel Worksheet Functions  7  January 17th 07 10:57 PM 
need help with expression  Peterpunkin  Excel Discussion (Misc queries)  5  May 4th 06 05:56 PM 
UDF to evaluate result of concatenate() with additional arg.  [email protected]  Excel Discussion (Misc queries)  2  January 13th 05 02:47 PM 