Using linked workbook as a parameter in a user defined function

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have created a function that needs a range as an
argument. It is similar to the VLOOKUP function, except
it can use more than one column as it's search key.
Anyway, when I call the function in a cell, and I use an
external reference to another workbook, it gives me
#VALUE!. If I have the external workbook open it works
fine, but as soon as I close it, and the argument in the
formula changes to the full path name of the file, it
fails. How can I use a full path name as a range argument
in a user defined function?

Thanx!
 
Hi Michael,
How can I use a full path name as a range argument
in a user defined function
Click to expand...

You could try putting the link in another cell and reference that cell
in the call to the udf. Another option is to define a name that refers
to the other workbook and use that name in the call to the udf.
e.g. :

Insert, name, define

Name: ExternalLink
Refersto:='c:\data\[test.xls]Sheet1'!$A$1:$c$4

Then in another cell:

=YourUDF(ExternalLink,Otherargs)

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Back
Top