Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup: file moved or new one. keep cache
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup: file moved or new one. keep cache
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;FAL SE) -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158752 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup: file moved or new one. keep cache
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. irealtymods wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup: file moved or new one. keep cache
=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/30...-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. joel wrote: 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;FAL SE) -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158752 Microsoft Office Help -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a cell even if its contents are moved moved/replaced | Excel Discussion (Misc queries) | |||
Open hyperlinks when file is moved | Excel Programming | |||
Someone moved my linked file | Excel Worksheet Functions | |||
A repost: Automatic file opening procedure seeming to hold cache | Excel Programming | |||
Automatic file opening procedure seeming to hold cache | Excel Programming |