Inconsistent updating of spreadsheet formula when changing file name

[Henk Stander]

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!

[:) Sixthsense :)]

Please post your formula for better understanding and correction.

[Henk Stander]

Please post your formula for better understanding and correction.

The formula works perfectly anywhere in the workbook, i.e. it updates before the file is saved when using "file save as", but when I copy it to another workbook with the same name (as applicable to the formula), it does not update before the file is saved, again when using "files save as".

The file name applicable to the formula is: "PSIM Model Tool Rev" and some value for after the "Rev", it is this part that the formula extracts.

Please Login or Register  to view this content.

Thanks

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

If you are saving the current formula file to new file then once you saved it with new name just press F2 and enter OR if you do any change in any of that workbook cell will get the current file name.

For 2003 use the below formula to get the file name and the notification message for unsaved workbook

Please Login or Register  to view this content.

For 2007 use the below formula to get the file name and the notification message for unsaved workbook

Please Login or Register  to view this content.

Hope that helps!

---

Sixthsense

[Henk Stander]

Hi Sixthsense,

Thanks for the response, it doesn't really solve my problem. See the attached spreadsheet for clarity of the problem I face.

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

[:) Sixthsense :)]

In your attached file in calculation sheet the vlookup lookup value is in error and sheet name is having error. Could you please tell me what is your lookup value and the #Ref! referred to which sheet?

=VLOOKUP(#REF!,#REF!B84:J119,8)

I don't find any error other than this. Please explain.

[Henk Stander]

In your attached file in calculation sheet the vlookup lookup value is in error and sheet name is having error. Could you please tell me what is your lookup value and the #Ref! referred to which sheet?

=VLOOKUP(#REF!,#REF!B84:J119,8)

I don't find any error other than this. Please explain.

Hi Sixthsense,
That is the thing, I don't care about the mentioned formula, but for some or other reason, if you delete it the updating of the formula on the first sheet does not work, i.e. it does not update while saving under a new name. Save the file under a new name, as per instruction and watch what happen with the value in bold. Repeat, but with the "vlookup" formula deleted, again watch what happen.

Thanks

[:) Sixthsense :)]

Hi Henk Stander,

It's silly!

Delete the calculation sheet from that file. Use saveas and save it in whatever the name you would like to save. Then once you have saved the file then it will show the previous file name. Just place the cursor in anywhere in any sheet of that workbook cell and press F2 and enter, you will get the new name.

Cell is a volatile function which gets recalculated every time if any change occur in the active workbookS. The "S" is intentionally typed in CAPS because you are missing the second part of the the cell function in your formula.

CELL("filename") (D7 Cell in your attachment) it is not the right way of using the cell function. Because cell is volatile and by using the cell function in your way will result error if the change is happening on any other active workbook. If that active workbook is saved file then this workbook CELL("filename") will result that active workbook filename or path.

If the active workbook is unsaved file then the function used on the workbook will result #VALUE! error.

So always mention the reference in cell function which you did in D5 cell. CELL("filename",A1)

Let me know whether this is what you are looking for or I am going out of subject.

Hope that helps!

[Henk Stander]

Hi Sixthsense,

You are helping, thank you. Please bear with me a little longer. 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 suggested by snb below, which will update the formula when opening the file again. It is however 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 somehow (don't really know how) link it to the updating of the value of the formula. If I do as you and snb suggest, 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.

Thank you

[:) Sixthsense :)]

Just a quick question whether the calculation is set to Manual?

[Henk Stander]

Just a quick question whether the calculation is set to Manual?

It is on Auto.

[Henk Stander]

I forgot to add snb's code, here it is:

Please Login or Register  to view this content.

[:) Sixthsense :)]

Sorry I could not able to understand your requirement. I hope someone may pitch in and give solution.

[Henk Stander]

Sorry I could not able to understand your requirement. I hope someone may pitch in and give solution.

Hi Sixthsense,

Thank you for all your help, I really appreciate it.

Have a great day!

[:) Sixthsense :)]

Thank you! But I will be happy if someone gives solution for your query.

[Henk Stander]

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.

Regards,
Henk