lose of referance

[fpssss]

Hello all,

I have a problem that sometimes when I save as an excel file all the formulas are referring (link) to the original file. I am not sure, when this is happening, but it is very annoying not be to be able to use the formulas when I open the excel sheet. Can you please let me know what might be the cause of that problem . I tried to save as, copy past to see when the problem is actually occurring but no idea.

Thanks!!

 

[alfranco17]

You might be typing and equal sign and then taking the values from workbooks different from the one you are saving.

If you copy and paste values, this will go away.

Also, you can go to Data / Links and remove the links.

 

[fpssss]

Thanks for quick response, but the think is if I remove the links, I loose the formula. I do not think that i copy pasted the formula to another sheet. I generally work on the same sheet and save to the different locations. But somehow it refers to the original sheet. Not all the time. Some where I make a mistake while saving or maybe somewhere else, but I do not know.

 

[alfranco17]

You are right, you would lose formulas.

If the files are the same but they refer to an older version of the same file, you can replace all references with Ctrl - H so that they point to the same file.
For instance: if you have:
=sum('[Older file]Sheet1'!a1:a10)
Replace [Older file] with nothing. Include the brackets. That will make the formulas point to your current file. If the sheet name does not exist you will get a #Ref error.

Thanks for quick response, but the think is if I remove the links, I loose the formula. I do not think that i copy pasted the formula to another sheet. I generally work on the same sheet and save to the different locations. But somehow it refers to the original sheet. Not all the time. Some where I make a mistake while saving or maybe somewhere else, but I do not know.

 

[fpssss]

That is a very nice idea! thanks.

 

[fpssss]

One small remark, excel can not find ' , it gives an error. all the links start with that.

 

[alfranco17]

You might be searching values. Press Ctrl H, go to advanced, select "look in formulas". That should fix it.

 

[fpssss]

yes I do the same thing, but when there is " ' " in the formula it can not find it. search results with an error.

 

[alfranco17]

I was able to find it. Could you pleas share somwhere your Excel fil or a portion of it with the formula?

 

[fpssss]

I do not know how to share a file here. But I tried one more time with and without it. Without it, it works just fine.