determine if a cell's value has changed

[jm5379]

is there a function (such as IFCHANGE(), CHANGED(), etc.) that tells if a cell’s value changes so a different cell can perform an action?

my spreadsheet tracks the current status of an online wargame. if the offensive troop count is changed (say from 3 to 24) i need it to display “Offense”; if the defensive troop count changes it needs to display “Defense”. the simplest example but it opens a very large door of possibilities if it exists.

i would rather not have to delve into macros or other more advanced areas at this time if possible.

thanks in advance.

[Lupp]

(The undo stack is something completely different, an I don’t think you can use it for your purposes.)

The only way to work based on changes I would know of is the usage of listeners by user code.
Concerning spreadsheets there is a simplified way to do it setting a routine to be called by a sheet event. Rightclick a sheet-tab to see what’s available.

Please note:
You can not use these events to decide if/when a cell was recalculated, and there is no acess to the previous values.
Only if the content was changed you have access to the set of cells for which this was done, but replacing a content with the same content by an action of editing is a change in the given sense, and so is clearing (Del) cells even if they were already empty (blank).
In no case information about the previous content is available if not explicitly saved elsewhere in advance.
You see: Documents, in specific spreadsheets are not history-aware.

[appreciatethehelp]

Is it a turn-based wargame? If so you’d need to track the troop count for each turn, therefore I propose an approach using six cells per turn: 2 for the offensive/defensive troop count LAST turn, 2 for the offensive/defensive troop count THIS turn, and 2 for displaying “Offense” or “Defense” via a formula dependent on the values of the “last turn/this turn” troop counts.

For example lets say cells A1, B1 and C1 contain the OFFENSIVE (last turn) troop count, OFFENSIVE (this turn) troop count, and the ‘OFFENSE display’ (i.e. cell that returns “Offense” when values change between turns), respectively.

The formula in C1 to display “Offense” in the event of the troop counts either increasing of decreasing between turns in this scenario would be =IF(A1<>B1, “Offense”, “”).

Conditional Formatting could also be utilised to draw attention to cell C1 by changing its colour if the text “Offense” is present, using the “cell value is” parameter i.e **cell value is: “Offense” apply coloured cell style.

Defensive troop counts could be approached the same way, say in cells A2, B2 and C2.

[jm5379]

i deeply appreciate both answers so far. i think Lupp’s answer more closely fits my needs since it’s not a turn-based game, merely keeping track of the enemy troop strengths during the current battle. i don’t need to keep track of prior numbers, only if the offensive or defensive counts have changed (it will only be one or the other, not a combined change).

it’s been years now since i’ve dealt with any level of programming so i’ll have to break down and study how to do routines (despite my best efforts to be as lazy as possible).

i’ll leave the thread open a while longer in case others may have alternate solutions. thanks again.