Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Evaluating results of a concatenate formula, as a formula
I have this formula in cell A2.
="='[Register " & VarName & ".xls]Monthly'!$D$20" Cell B1 has a range name of "VarName" and it contains "2005a". The result I get is a string: ='[Trust Register 2005a.xls]Monthly'!$D$20 Rather than displaying the string, I want the string to evaluate as a fomula. Any ideas ? |
#2
|
|||
|
|||
On Thu, 8 Sep 2005 19:46:02 -0700, dodger
wrote: ="='[Register " & VarName & ".xls]Monthly'!$D$20" So long as the 'Register 2005a' workbook is open, you can use the INDIRECT worksheet function: =INDIRECT("'[Register " & VarName & ".xls]Monthly'!$D$20") --ron |
#3
|
|||
|
|||
Hi
Copy cell A2, and PasteSpecial Values From Edit menu, Replace '=' with '=' -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "dodger" wrote in message ... I have this formula in cell A2. ="='[Register " & VarName & ".xls]Monthly'!$D$20" Cell B1 has a range name of "VarName" and it contains "2005a". The result I get is a string: ='[Trust Register 2005a.xls]Monthly'!$D$20 Rather than displaying the string, I want the string to evaluate as a fomula. Any ideas ? |
#4
|
|||
|
|||
Thanks for the reply. I'm going to have 60 of these spreadsheets that pull
data from different files and there will be several formulas similar to this one in each file. I'm trying to make the formulas dynamic enough that I don't have to edit each of those formulas. "Arvi Laanemets" wrote: Hi Copy cell A2, and PasteSpecial Values From Edit menu, Replace '=' with '=' -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "dodger" wrote in message ... I have this formula in cell A2. ="='[Register " & VarName & ".xls]Monthly'!$D$20" Cell B1 has a range name of "VarName" and it contains "2005a". The result I get is a string: ='[Trust Register 2005a.xls]Monthly'!$D$20 Rather than displaying the string, I want the string to evaluate as a fomula. Any ideas ? |
#5
|
|||
|
|||
Thanks for your reply. I tried that and the result was #Ref!. Also, because
there will be several of these formulas in this workbook referencing different files, having them all open will not be practical. "Ron Rosenfeld" wrote: On Thu, 8 Sep 2005 19:46:02 -0700, dodger wrote: ="='[Register " & VarName & ".xls]Monthly'!$D$20" So long as the 'Register 2005a' workbook is open, you can use the INDIRECT worksheet function: =INDIRECT("'[Register " & VarName & ".xls]Monthly'!$D$20") --ron |
#6
|
|||
|
|||
On Fri, 9 Sep 2005 04:30:03 -0700, dodger
wrote: Thanks for your reply. I tried that and the result was #Ref!. Also, because there will be several of these formulas in this workbook referencing different files, having them all open will not be practical. "Ron Rosenfeld" wrote: On Thu, 8 Sep 2005 19:46:02 -0700, dodger wrote: ="='[Register " & VarName & ".xls]Monthly'!$D$20" So long as the 'Register 2005a' workbook is open, you can use the INDIRECT worksheet function: =INDIRECT("'[Register " & VarName & ".xls]Monthly'!$D$20") --ron That means that either there was no workbook open with the name 'Register 2005a.xls' or that there was not worksheet named Monthly in that workbook. But if you cannot have the workbook open, then you cannot use the INDIRECT function. However, you could download Laurent Longre's free morefunc.xll add-in from http://xcell05.free.fr/ and use the INDIRECT.EXT function. This will allow referencing closed workbooks. You may need to add more information to "Register" to more fully define the path name. See HELP for that function to decide if you need to do that. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
CONCATENATE formula | Excel Discussion (Misc queries) | |||
Help with a formula for concatenate and search/find with 3 columns | Excel Worksheet Functions | |||
How do I prevent incorrect formula results appearing in cell? | Excel Worksheet Functions | |||
If Greater than when a formula Results in N/A | Excel Worksheet Functions |