Why has this formula stopped working?

July 28, 2016 at 01:03:14
Specs: Windows 7
I have been using this for a while:

=IF(J4="yes", IF(K4="",TODAY(), K4),"")

when "yes" is entered in J4, today's date is inserted in K4. This date persists when spreadsheet is opened on later dates. It uses a circular reference, but worked fine.

For some annoying reason it has now stopped working. Any ideas?

edit: sorry should have mentioned this formula is entered into cell K4.

(Excel 2010 on Windows 7)

message edited by purpletallboy

See More: Why has this formula stopped working?

Reply ↓  Report •

July 28, 2016 at 02:48:17
Think I just resolved this myself, but has raised another question.
I have turned on iterative calculation in options/formulas and it works again.
The new question is can this spreadsheet be made to turn on iterative calculation when it is opened on another PC with a different copy of Excel which may not have iterative calculation turned on!??! Thanks for any advice.

Reply ↓  Report •

July 28, 2016 at 04:32:48
I'm not sure if a macro could turn on that setting, but even if it could, macros would have to be enabled on the other PC. If macros are not enabled, only the user can enable them.

It would be a huge security risk if a macro could enable macros on their own.

There is a "trick" to urge users to turn on macros, such as a workbook with a "splash screen" sheet that opens informing the user that the workbook is useless without macros enabled. All other sheets are hidden/protected until macros are enabled.

However, the real question is:

Why do you have to use the same cell for your input as well as your date?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Reply ↓  Report •

July 28, 2016 at 07:37:53
When you entered your formula into cell K4 did you receive a warning about Circular References?

As you have found, turning on Iterative Calculations solves the problem, but the one major problem with using circular references is once you have a circular reference it is very difficult to differentiate between mistakes that have created an accidental circular references and the intentional circular reference.

It is best used with caution and an understanding of how Iterative Calculations operates and how it could adversely effect your work.



Reply ↓  Report •

Related Solutions

Ask Question