How to stop Excel from changing a file path in a formula

save as a template, not Week##

Dear tim201110,
Thanks for the reply and the suggestion, however I am unable to influence how the files are saved or copied.
There are a few work arounds I can use, but I really would like a way to stop excel from changing my formulas.
Is there a way to make a file path in a formula absolute, perhaps using &?
My laptop is about to end up in the fishtank ;-)

Dear Ali,
Thanks for the suggestion, which would be perfect except that using indirect will return an error if the source files are closed, and I am not able to open them as they are too large. There is no logical reason why using indirect in the destination workbook won't allow linking to source closed files - but for some reason excel wont allow this !
Don't worry, I am sure that my laptop will make a good ornament for my fish to swim around, and at least the snails will keep the screen clean.

Hi Ali,
Thanks this works with Indirect, but only when the source file is open. Apparently this is an issue with indirect that other bloggers have also seen. It doesnt make sence. Otherwise this would be the perfect solution. It seems that whatever I try, excel has some little quirk that gets in the way. I now have the ifferror in my formula that says "grrr" when the error occurs. Have you seen this issue with indirect where the source file has to be open?

Only cell references can be made absolute. Worksheet and Workbook references are linked to the object, not the name, this means when you change the name the formula will follow.
Think how many people create formulas that cross reference other workbooks / worksheets and how easily people could break those formulas by simply changing the name of one of them. The problem you are encountering is annoying but deliberate, the software engineers that created excel would have thought of things like this and catered for what is undoubtedly the more common of the 2 situations.

The sensible fix for this is to educate users and change the way that the file is saved / created as Tim has already suggested.

Do you want to keep the formulas the same, or just keep the values?

I ask this because in the situation that you describe in post #1

Originally Posted by david killoran

Or alternatively if someone changes the file name of week 1.xlsx name to week 2.xlsx.

Now when this happens, excel automatically changes the file path in my formula to ='C:\Documents\[Costs for Week 2.xlsx]sheet1'!$A$1

But I want the formula to stay as I typed it i.e. ='C:\Documents\[Costs for Week 1.xlsx]sheet1'!$A$1

If the user changed the name from week 1 to week 2 then the original week 1 file would no longer exist, so there would be nothing for the formulas to look at if they were still looking for week 1.

Copy - PasteSpecial values would be a simple fix in this case.

Jason/Ali thank you for your advice,
Jason in answer to your question, my worksheet gets values for all of the weeks, and the weeks where the file does not exist just comes up with a blank due to the iff error part off the formula. The idea is that when a new week e.g. week 2.xlsx is created, the spreadsheet will then automatically get the values. (but if week 2 is created by "saving as" week 1, then the formula will change and I will get week 1's values because the formula will change. If you think that the there is no way to make a file path absolute, then I will try and find a workaround. I have just created a vba code that will keep re-inserting all of the formulas - messy but I can't think of another way.. grrrrr.... Solutions that use "Indirect" and "names", don't work on closed workbooks. I cant' help but feel that the quantum world of Microsoft excel is conspiring against me...........

Find and Replace (ctrl h) can be used to change part of a formula.

Not knowing your sheet layout, this is only a theoretical method that I would use for the task.

Assuming that you have the formula, ='C:\Documents\[Costs for Week 1.xlsx]sheet1'!$A$1 in A1, with the correct week '"Week 1' in B1, either apply a helper column or conditional formatting (preferred) using the formula =ISERROR(SEARCH($B1,FORMULATEXT($A1))) to highlight cells where the formula doesn't match the correct week number, then select those cells and replace Week 2 with Week 1 using ctrl h.

Did you ever figure out how to make Excel stop changing the file path? I am having the same issue when I "save as" to a different file name. I need to keep the original file path and also need to rename the file. Thanks.

RJ