Solved: Inconsistent updating of spreadsheet formula when changing file name

[vanhunk]

Hi there,
I have a formula in a spreadsheet that successfully extract part of the file name. When I change the file name by using "save as" and the new file name, it successfully updates the formula before the file is actually saved.

Now, my problem, I can't replicate this behavior. I use exactly the same formula in a different file and it does what it is supposed to do, HOWEVER it does not update before the file is saved under a new name. It only updates when I recalculate the spreadsheet after the file has already been saved. PLEASE HELP!

[snb]

Which method do you use ?

[vanhunk]

Hi snb,

THIS IS SERIOUSLY WEIRD!

I thought this problem was VBA related, but I changed my mind. I have removed everything from the spreadsheet that does not have an effect until I was left with the bare minimum. I attached the file for clarity.

In the attached file:
For some or other reason the of the sheet called "Calculation" makes this happen.


You can also not delete the formula in error on the "Calculation" sheet either.


So as long as the sheet "Calculation" with its formula in error is present,
the formula updates as required, i.e. before the file is actually saved under a different name.
PLEASE EXPLAIN, BECAUSE THIS COULD BE VERY USEFUL!!!


To see how it works, save this file under a different name, keep everything the same but change what comes after the "Rev", i.e. save it as, for example, "PSIM Model Tool Rev Test2.xls"


Regards,
vanhunk

[Bob Phillips]

Always anchor the formula to the sheet

CELL("filename",A1)

[vanhunk]

Thanks xld, I will keep that in mind. It does not however address the problem.

Regards,
vanhunk

[snb]

I introduced:

[vba]Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Application.Calculate
End Sub[/vba]

I can't test the formula in sheet Calculation because it is referring to a unknown object #REF
It was no problem removing that formula.

[vanhunk]

Thanks snb,

You are not supposed to test the formula with the "#REF!", for some or other reason it is the fact that the result of the formula is "#REF!" what is making the other formula with the "cell("filename",A1)" content to work as required, i.e. to change the value of the formula BEFORE it is saved.

The problem I have is that doing it the way you suggested leaves the saved file not updated, unless you save the file again, which I would not like to depend on. You can also add the code as you suggested, which will update the formula when opening the file again. It is however still leaving the saved version not updated and again relies on saving it again. This could be fine on its own, but what I would really like to achieve is to have a date value in cell D3 which will update only when the file is saved under a new revision. I want to link it to the updating of the value of the formula. If I do as you suggested, this date value will only update when the file is opened up again and thus give the wrong date as date of revision, and still rely on saving the file again. Thus, I would like to understand what is happening at the moment and try to use it to get to something that is not silly, but will still do the job.

I have discovered something very interesting and I would like to understand it. What I have discovered is, that as long as there is a formula, any formula, somewhere in the workbook that has a result of "#REF!", formulas that use "cell("filename",A1)" anywhere in the formula will be updated before the file is saved under a new name, while saving. It is actually quite useful. It is also making what I am trying to achieve possible.


Thanks again for your support

[snb]

[VBA]Sub snb()
Application.CalculateBeforeSave = True
End Sub[/VBA]

[vanhunk]

Hi snb,

Tried that already, does not do it. Does not update the formula with the new name.