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 |
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") |
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 |
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 |
All times are GMT +1. The time now is 01:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com