Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 3rd 09, 08:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
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

  #2   Report Post  
Old November 3rd 09, 09:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
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   Report Post  
Old November 3rd 09, 10:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
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   Report Post  
Old November 4th 09, 05:52 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,909
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 09: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 01:47 PM


All times are GMT +1. The time now is 11:25 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017