Excel Linking Formula Unable to Read File When Change Source 1

[Lilliabeth]

We have been sent two workbooks, one contains VLOOKUPs that refer to data in the other, the source workbook is called Master.xls. The relevant sheet in Master.xls is hidden and the workbook is protected with a password, so I cannot see the data.

We want to save these workbooks and change the VLOOKUPs so they refer to our copy of Master.xls, but we click Change Source and select Master.xls and then get a message: Excel cannot read the file. Continuing on anyway changes the first formula only, and changes it to an NA error.

can anyone offer advice?

--Lilliabeth

 

[xlhelp]

Hi, Lilliabeth.

How about renaming their Master.xls? Then open your version of Master.xls and then open the linked file and try to change source. Hopefully, everything is in the same folder.

Or is that too simple?

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I thought I had retired...I was mistaken. I used to work for a large corporation; now I work for my wife.!

 

[Lilliabeth]

Thank you xlhelp; that was a big help. We had tried that without success, but you gave me an idea...
We had also tried to use the Replace tool to replace the old path with the new path, but had gotten the same 'unable to read' error. Your suggestion gave me the idea to open Master.xls and then try Replace. It worked. Change source didn't work, master open or closed. Replace worked but only when master was open.
Thanks again!

--Lilliabeth