Evaluate String Path as value

[scottscott]

Is it possible to evaluate the below statement as a formula in a cell:

= "=" & A1 & LEFT(A3,2) & "00 - " &LEFT(A3,2) & "99\[HIL " & A3 & " " & B3 & ".xlsx]Composition'!$L$1"

Where A1 is a server location and A3 & B3 are just numbers. So the end result will look something like this:

='\\ServerA\2800 - 2899\[HIL 2800 8-069.xlsx]Composition'!$L$1"

Ideally I would like the formula to return the value from file "HIL 2800 8-069.xlsx" from sheet "Composition" from location $L$1. I tried using the evaluate function and defining a formula in the name manager but I it just returns #value. Cell C3 contains the formula I am trying to evaluate.

In the name manager I tried:

Result = Evaluate($C3)
Then Cell C4 would equal =Result

Is there anyway to evaluate this string as a link to the cell in a worksheet without using vba. If I have to use vba to evaluate the cell how would I accomplish that task?

Thanks,

E

 

[SkipVought]

hi,

Is there anyway to evaluate this string as a link to the cell in a worksheet without using vba.

1) Select
[tt]
= [highlight]"=" & A1 & LEFT(A3,2) & "00 - " &LEFT(A3,2) & "99\[HIL " & A3 & " " & B3 & ".xlsx]Composition'!$L$1"[/highlight]
[/tt]
in the formula bar
2) hit F9
3) observe the result in the formula bar
4) hit ESC to return the formula!!! WITHOUT FAIL!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

You might also want to look at the INDIRECT function.

 

[scottscott]

Thanks a lot Skip! I would have never been able to figure that out. I just read up on all the Function keys. I always forget about those.

Strongm,

I tried the indirect function earlier but wasn't able to get the string to evaluate. Of course you could use the F9 trick that skip just showed and the indirect function but it wouldn't be any different than the original formula purposed. If you have a method which would evaluate without pressing the F9 key then I would be definitely interested.

Scott

 

[SkipVought]

Do not include the leading EQUAL sign in the INDIRECT argument

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

INDIRECT has issues with external closed workbooks so probably not a solution in this case given the "xlsx" aprt of the formula...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[strongm]

>INDIRECT has issues with external closed workbooks

Very true

 

[SkipVought]

I'd use MS Query to get either a tabular resultset from the external workbook or a single value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]