External reference changed to #Ref when source is opened

[johndoexxiv]

I am having problem with my friend's excel sheets. My friend is trying to have 1 cell from his sheet p1 reference to sheet p2. So he wrote something like this.

='\\192.168.0.207\folder1\folder2\[p2.xlsx]Sheet1'!C1

There is no problem referencing the correct cell in normal circumstances.

However, when the source file is opened, the formula changed to something like this:

='[p2.xlsx]#REF!'C1

I check the folder path is correct. The file doing the reference (p1.xlsx) is password protected, but not the source file. I tried to re-link the source using "Edit link" but no use. Can anyone help me with this problem?

[:) Sixthsense :)]

It seems that the referred sheet of the source is getting deleted.

[FDibbins]

does the source file have a sheet names sheet1?

[johndoexxiv]

It seems that the referred sheet of the source is getting deleted.

No. File is still there and I tried to retype the link. No use

does the source file have a sheet names sheet1?

Yes. There is. I am able to link to that sheet/file until the source is open then it became #ref

[:) Sixthsense :)]

Don't retype it.

Just delete the current formula cell and type = and take your mouse to that source file and click the cell.

Close the source workbook so that it will show the complete path if you wish to see..

Whenever the source file is in open the root path of the source file won't be shown in formula bar.

[FDibbins]

Good thinking Six

[:) Sixthsense :)]

Good thinking Six

Ah!! Thank you

[FDibbins]

Lets hope if fixes the problem

[johndoexxiv]

I was trying to use the method that you suggested, but I found out that the problem that I mentioned did not happened on my computer and only on his. I current can not try on his computer. I will try re-referencing the way you said later.

However, I have notice something funny btw. Our excel is stored on server. So the path to those file is "\\192.168.0.206\userdata\abc\p1.xlsx" and "\\192.168.0.206\userdata\abc\p2.xlsx".

My x drive is mapped to \\192.168.0.206\userdata. His Y drive is mapped to \\192.168.0.206\userdata. When p1.xlsx is opened my computer, the formula shows x:\abc\p2.xlsx. On his computer it shows Y:\abc\p2.xlsx.

So I am wondering how exactly does excel store those link? Do they store the drive letter? or the IP address? We didn't make the excel ourselves so there is no way we know what it is entered initially. Will the formula change automatically if we change the drive letter?

Many thx

[:) Sixthsense :)]

It depends how both of your system drives mapped on the network…

[johndoexxiv]

I just tried using your method. it seems it is working. But he have at least a hundreds link to 10-20 files. is there anyway that can fix this without doing it one by one?

[:) Sixthsense :)]

Make use of Find & Replace which you can get by pressing Ctrl+H

Example ='[p2.xlsx]#REF!'C1

In the above case the #REF has occurred in Sheet Name Reference.

So press Ctrl+H and in find what box type #REF and in replace with type your sheet name like MySheet

Expand the Options Button>> In Look in>>Select Formulas and click Replace All.