I'm an avid Excel user and recently I've had to figure out how to use Numbers. So far I've been able to replicate all the Excel functionality that I use in Numbers… except for one thing.
In Excel I can add the following formula…
=IF(A1="", "", IF(B1="", NOW(), B1))
This formula says when a value in added to cell A1 populate cell B1 with the current date. To get this to work you must turn on an Excel feature named iterative calculation (Options==>Formulas==>Enable iterative calculation).
I use this when I'm building an Excel task journal and I want to auto-populate the date.
The important thing about this formula is that the date value is not updated another change is made.
How can I do this in Numbers? How can I auto populate a date value in column "B" when a value is set in column "A"? It's important to keep in mind that once the date is set I don't want it to change.
Here is my attempt at translating this formula in Numbers…
=IF(A2="", "", IF(B2="", NOW(), B2))
I get an error message in Numbers…
This formula can't reference its own cell, or depend on another formula that references this cell.
Thanks
Best Answer
I offer this answer only because at least it provides a workaround, albeit a slightly manual one.
Assuming that B1 is the cell that needs to have the date and time auto-populated when cell A1 is filled in, enter this formula in cell B1:
Now, when the time comes that cell A1 is filled in, cell B1 will auto-populate with the date and time.
However, to prevent cell B1 from updating if/when cell A1 is edited again, you now need to do as follows:
NOTES:
In case this is useful for anyone else trying to come up with an answer, I also thought of:
using an AppleScript script to replicate the above, but then realised that using a script would be no quicker that just using the Copy/Paste functionality within Numbers.
creating an Automator service and then allocating a keyboard shortcut to that, but that too would be no quicker as you'd still need to select the cell, so using the built-in shiftcommandV shortcut is no slower than using a customisable shortcut.
Regardless, someone more adept at AppleScript may think of a more elegant solution that works with Numbers to achieve the OP's goal.