Using a Named Range as Parameter for External Links in Excel 2007

If this is working:

=VLOOKUP($I13;'\\sap01\data_new\Organisation\Marketing_Sales\Sales\Sales_Funnel\Forecast and Marketview Tool 2010 SAP-Data.xlsm'!CostOutput;N$523;FALSE)

then, if A1 has your external file path/name and B1 the range in that file...

=VLOOKUP($I13;INDIRECT(A1&"!"&B1);N$523;FALSE)

That is,

A1       '\\sap01\data_new\Organisation\Marketing_Sales\Sales\Sales_Funnel\Forecast and Marketview Tool 2010 SAP-Data.xlsm'
B1       CostOutput

This is exactly what I tried before but I get a #REF!-Error!

Q3 is set to '\\sap01\data_new\Organisation\Marketing_Sales\Sales\Sales_Funnel\Forecast and Marketview Tool 2010 SAP-Data.xlsm'

Q4 is set to CostOutput

my formula looks like this:
=VLOOKUP($I13;INDIRECT(Q3&"!"&Q4);N$523;FALSE)
and it returns a #REF!-Error

One strange thing I noticed in the Evaluate Formula Tool:
-> see attached screen shot
It shows slightly incorrect quotes. Any ideas why?


Evaluate-Formula.jpg

Excel might be treating that first singlequote as a text qualifier.  Try entering this in Q3 instead:

="'\\sap01\data_new\Organisation\Marketing_Sales\Sales\Sales_Funnel\Forecast and Marketview Tool 2010 SAP-Data.xlsm'"

Thanks teylyn

The issue with the two single quotes at the beginning of the string I should have come across myself and I feel quite a bit ashamed of this ....

But I was not aware of the fact that INDIRECT() only works on open workbooks and this is nor really obvious to me.
Thanks for the hint with Morefunc. This Add-In seems to be quite helpful. But I probably won't use it because the workbook will be used on PCs that don't have Morefunc installed and I don't like the constraints to include Morefunc in the workbook.
I will probably write a macro that opens the source file and use the standard INDIRECT()  Function.
It might be faster anyway, as there are about 100'000 lookups.
What's your opinion on this? Are there any other issues to consider?

Thanks and have a nice day
gpsfsc

Thanks for the feedback and the grade, gpsfsc!!

Hi teylyn

Sorry to bother you again ....
I did some tests with INDIRECT.EXT and it works as promised but it is soooooooooooooooooooooooooo incredibly slow! Originally, with the direct path entries it took about 10 seconds to perform some 100'000 lookups in a closed workbook. Now with INDIRECT.EXT it takes EXCEL almost half an hour to update about 4000 lookups!
WHAT AM I DOING WRONG ?   Is there a way to accelerate this?

Hear from you soon
gpsfsc

Well, unfortunately, INDIRECT is volatile, and so is INDIRECT.EXT

There may be ways to streamline the spreadsheet and improve performance, but it may require a redesign.

Have a look here for some insight on how to improve Excel performance

http://msdn.microsoft.com/en-us/library/aa730921.aspx

cheers, teylyn

Hi teylyn

INDIRECT.EXT has a parameter that can be set to behave volatile or not anf I have set it to non-volatile.
When I tried it it was with the source file closed. I'll give it a try with the source file open. If it doesn't improve, I have find some other way.