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

I have the following formula:
=VLOOKUP($I13;'\\sap01\data_new\Organisation\Marketing_Sales\Sales\Sales_Funnel\Forecast and Marketview Tool 2010 SAP-Data.xlsm'!CostOutput;N$523;FALSE)
and this works as expected.
Now I would like to replace the external link
'\\sap01\data_new\Organisation\Marketing_Sales\Sales\Sales_Funnel\Forecast and Marketview Tool 2010 SAP-Data.xlsm' by a value in a cell  or Named Range.
I tried a few things with INDIRECT() but nothing did the trick.
gpsfscAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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
0
gpsfscAuthor Commented:
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
0
Patrick MatthewsCommented:
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'"
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello gpsfsc,

it is sufficient to precede your file path with a single quotation mark, so, effectively you have two single quotes at the beginning of Q3

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

The other thing you need to keep in mind is that Indirect() only works with open workbooks, so you will always get a #REF! error if the other workbook is closed.

To bypass that, you can install morefunc.xll, a free collection of functions, among them INDIRECT.EXT(), which works with closed workbooks, too.

The download is here: http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

cheers, teylyn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gpsfscAuthor Commented:
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
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Thanks for the feedback and the grade, gpsfsc!!
0
gpsfscAuthor Commented:
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
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
0
gpsfscAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.