Vlookup Roadmap - for lack of a better description

[Grae]

I have roughly thirty workbooks that all Vlookup to one master schedule for data. The problem is that when the master schedule gets revised and subsequently renamed, all the formulas need to be updated. I was wondering if it was possible to have an intermediate workbook that has the master schedule's workbook address in a cell, and have the Vlookups using that cell as a roadmap to the latest revision. Then once the master schedule was revised, only the one cell would have to be revised to change all the formulas in all thirty workbooks.

Thanks for your help,

Graeme

 

[WhoCanDo]

Would a find (ctrl-F) and replace be suitable to change the file name in the vlookup function?

 

[Grae]

Hi WhoCanDo,

Thanks for your reply. The find/replace does work, but unfortunately requires the opening of all thirty workbooks...I'll try to do a better job of explaining, back in a bit.

Thanks,

Grae.

 

[Smitty]

Welcome to the Board!

Instead of Find/Replace, you can goto EDIT-->Links and point the dependent workbook to the new one. That will update all of the formulas in the entire workbook at once.

If that does what you want, then you can record a macro doing the same thing on the next one, which will capture the correct file path for the revised link. Post back with what you get and someone can give you some code to loop through all workbooks in a folder and apply what you came up with.

HTH,