vlookup: file moved or new one. keep cache

[irealtymods]

i have vlookup formula to external file, but what if that file moved or
renamed?? how to keep vlookup to find what i need?? there is formula extra
properties??

and is it possible to keep data once received? some cache, i mean if i have
some table full already, all needed data is taken from other file, do not
update this table any more.


there is any better alternative to this formula + changes above?
=VLOOKUP(K29;'C:\Data.xls]Data'!$A$5:$Z$568;12;FALSE)

thank you.

 

[joel]

You can use indirect to make the workbook a parameter which you can pu
into cell A1. then you only have to change the book name in cell A1 an
all the formulas will automatically reference the new workbook.


From

=VLOOKUP(K29;'C:\Data.xls]Data'!$A$5:$Z$568;12;FALSE)

to

Cell A1 : C:\Data.xls
=VLOOKUP(K29;indirect(A1&"Data!$A$5:$Z$568";12;FALSE

 

[Dave Peterson]

If the file is moved or renamed, then excel won't have any idea where to look
for it. (It could have been deleted!)

You can use Edit|Links (in xl2003 menus) to change the source of the links.

If you mean that you don't need to update the values returned by your =vlookup()
formula, you could convert them to values.

i have vlookup formula to external file, but what if that file moved or
renamed?? how to keep vlookup to find what i need?? there is formula extra
properties??

and is it possible to keep data once received? some cache, i mean if i have
some table full already, all needed data is taken from other file, do not
update this table any more.

there is any better alternative to this formula + changes above?
=VLOOKUP(K29;'C:\Data.xls]Data'!$A$5:$Z$568;12;FALSE)

thank you.

 

[Dave Peterson]

=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.

You can use indirect to make the workbook a parameter which you can put
into cell A1. then you only have to change the book name in cell A1 and
all the formulas will automatically reference the new workbook.

From

=VLOOKUP(K29;'C:\Data.xls]Data'!$A$5:$Z$568;12;FALSE)

to

Cell A1 : C:\Data.xls
=VLOOKUP(K29;indirect(A1&"Data!$A$5:$Z$568";12;FALSE)

--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=158752

Microsoft Office Help