Flexible formula for linking to externa Files

[Tami]

hi,
I have a file that has (for simplicity) 5 columns: Dept, Class, Style,
Color, Units Ordered

The Units Ordered column should be a formula to a file named C:\Deptxx, with
the xx being a two-digit dept number (e.g. C:\dept10, C:\dept12, C:\dept15
etc)

so based on the two digits keyed in column 1 (Dept) the Units ordered column
should be a vlookup to file C:\deptxx. so if someone typed 10 for dept
number the formula is smart enough to look in c:\dept10.

do i do some concatanation to get this done or is there a better way?
Thanks in advance for any help!
Tami

 

[JP]

So if your lookup value was in D1 and the cell with the two-digit
number was in A1, it would go something like:

=VLOOKUP(D1,"C:\Dept"&A1&" the\rest\of\your\link!Range",2,FALSE)

If you enter 10, this should concatenate to =VLOOKUP(D1,"C:\Dept10 the
\rest\of\your\link!Range",2,FALSE)


--JP

 

[Dave Peterson]

The function you'd want to use is =indirect().

But =indirect() won't work if the sending file is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.