Can a VLOOKUP reference automatically update? 1

[shawnmandel]

I have a Workbook2.xls with VLOOKUP references to Workbook1.xls. If a user goes ahead and changes/re-saves Workbook1.xls to a new filename, say Workbook1-new.xls, is there any way to have the VLOOKUP in Workbook2.xls updated as per the newly named file to Workbook1-new.xls?

Any help would be greatly appreciated.

Thanks,
Shawn

 

[Zathras]

From Workbook2.xls, use Edit/Links... to change the reference to Workbook1.xls to the new workbook name.

 

[shawnmandel]

In need this VLOOKUP update done as automated as possible. How could this update be performed with VBA code to be userfriendly to a user who is expecting this VLOOKUP to work on a renamed Workbook?


Thanks for your reply Zathras,
Shawn

 

[xlbo]

They'd still have to browse for the workbook to reference - there will be no inherent link. The best you could do would be to programatically open up the file browser for them to be able to select which file to use....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[shawnmandel]

Thanks xlbo. At least I know that there is no way to do it other than prompting the user for the file.

Regards,
Shawn

 

[Gavona]

You are probably aware of this but just in case: If Workbook2 is open when workbook1 is saved with a new name then the references in workbook2 will automatically update.

I guess you could use the WorkbookBeforeSave event to make sure the linked workbook (workbook2) was open. However that is currently beyond me!

Regards

Gavona

Good luck.
Gavona