A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

evaluate a concatenate expression



 
 
Thread Tools Display Modes
  #1  
Old November 3rd 09, 09:41 PM posted to microsoft.public.excel.worksheet.functions
Russell.Ivory[_2_]
external usenet poster
 
Posts: 4
Default 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  
Old November 3rd 09, 10:31 PM posted to microsoft.public.excel.worksheet.functions
Glenn
external usenet poster
 
Posts: 1,240
Default 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  
Old November 3rd 09, 11:26 PM posted to microsoft.public.excel.worksheet.functions
Russell.Ivory[_2_]
external usenet poster
 
Posts: 4
Default 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  
Old November 4th 09, 06:52 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,911
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 05:13 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.