EVALUATE in defined name question

[Ken Shaffer]

I'm trying to use a defined name containing an EVALUATE(VLOOKUP...). The
VLOOKUP is referencing another spreadsheet in a path based on a date, and
whose name is also based on a date.

The defined name works perfectly if I just cut and past the VLOOKUP directly
and use the name in a cell.

However, when trying to dynamically determine the correct path and name
based on a cell containing the date, I get a #REF! error.

In stepping through the evaluation process, the only difference I see
between the two is that the dynamic one is a string to be evaluated and the
quotes that are part of what I want to lookup are quoted (two quotes for
one).

Any ideas?

 

[Bob Greenblatt]

I'm trying to use a defined name containing an EVALUATE(VLOOKUP...). The
VLOOKUP is referencing another spreadsheet in a path based on a date, and
whose name is also based on a date.

The defined name works perfectly if I just cut and past the VLOOKUP directly
and use the name in a cell.

However, when trying to dynamically determine the correct path and name
based on a cell containing the date, I get a #REF! error.

In stepping through the evaluation process, the only difference I see
between the two is that the dynamic one is a string to be evaluated and the
quotes that are part of what I want to lookup are quoted (two quotes for
one).

Any ideas?

Ken,

It's pretty hard to visualize what you are trying to do without a specific
example. However, remember that references in the defined name dialog are
usually relative to the active cell. If the formula works fine on the
worksheet, make sure you are using absolute references $A$1 for example in
the refers to field.

If this doesn't help, repost with some specifics of what you are tru=ying to
accomplish.

 

[Ken Shaffer]

....
It's pretty hard to visualize what you are trying to do without a specific
example. However, remember that references in the defined name dialog are
usually relative to the active cell. If the formula works fine on the
worksheet, make sure you are using absolute references $A$1 for example in
the refers to field.

My defined names:

archive ="'\\explr_drivers5\Reboot_results2"
AustinDVR=DVR1&"\austin\"&Date&"\[uptimepwr_Austin_"&Date&".xls]exec_summary
'!$B:$R,17,FALSE)"
Date =TEXT(' %_all days'!C$2,"yymmdd") $C$2 contains 05/10/2004
DVR1 ="VLOOKUP("&CHAR(34)&"DVR"&CHAR(34)&","&archive
Test =EVALUATE(AustinDVR)
Test_Works=EVALUATE(VLOOKUP("DVR",'\\explr_drivers5\Reboot_results2\austin\0
40510\[uptimepwr_Austin_040510.xls]exec_summary'!$B:$R,17,FALSE))

I'm entering =Test in $C$7 and getting back #REF!

If I enter =Test_Works, I get the expected value from the referenced
spreadsheet.

When I step through the evaluation process, the AustinDVR has the same
string value as what's in the Test_Works defined name.

As you can see, I'm trying to use defined names to dynamically choose which
spreadsheets for what date I need data from.

(I just noticed I'm posting to a MAC group but hope someone here can help)

 

[Bob Greenblatt]

...

It's pretty hard to visualize what you are trying to do without a specific
example. However, remember that references in the defined name dialog are
usually relative to the active cell. If the formula works fine on the
worksheet, make sure you are using absolute references $A$1 for example in
the refers to field.

My defined names:

archive ="'\\explr_drivers5\Reboot_results2"
AustinDVR=DVR1&"\austin\"&Date&"\[uptimepwr_Austin_"&Date&".xls]exec_summary
'!$B:$R,17,FALSE)"
Date =TEXT(' %_all days'!C$2,"yymmdd") $C$2 contains 05/10/2004
DVR1 ="VLOOKUP("&CHAR(34)&"DVR"&CHAR(34)&","&archive
Test =EVALUATE(AustinDVR)
Test_Works=EVALUATE(VLOOKUP("DVR",'\\explr_drivers5\Reboot_results2\austin\0
40510\[uptimepwr_Austin_040510.xls]exec_summary'!$B:$R,17,FALSE))

I'm entering =Test in $C$7 and getting back #REF!

If I enter =Test_Works, I get the expected value from the referenced
spreadsheet.

When I step through the evaluation process, the AustinDVR has the same
string value as what's in the Test_Works defined name.

As you can see, I'm trying to use defined names to dynamically choose which
spreadsheets for what date I need data from.

(I just noticed I'm posting to a MAC group but hope someone here can help)

Try Indirect instead of evaluate. What happens if you place the refers to
strings in calls and then concatenate the cells? Do you get the correct
strings and results?