“…related cells” was the challenge.
This will do as a mock requirement:
- As a user checking their payslips
- I want formatting in a spreadsheet to alert me to a change in an element of my pay or deductions from month to month
- So that I don’t have to focus on the numbers to do that
Show me if the current month’s element differs from the previous month: I don’t care if it goes up or down, just that it changes.
This is our spreadsheet before any formatting is applied. Note that the changes (basic pay, tax code, tax) are kind of small, so not that obvious.
First, we’ll apply the principle to cell B4 relative to cell B3, as we can see there is a change. (Assumption: the basics of conditional formatting in a spreadsheet are not new to you). Right click the B3:B4 range, and pick Conditional formatting:
This is the default dialog from that:
We don’t care about the alert colour, so we’ll leave as is. Now get to the highlighted point below which says “B4 does not equal B3”:
Trouble is… that is a literal reference, so when I copy it down, it will take “=B3<>B4” with it. (This is not the same as an absolute reference, i.e. $B$3 style).
We now have to introduce the technique which dynamically assesses the intended cell, and that means bringing in “indirect()” and “row()” (“col()” would be needed in a different context – this is fine for me).
Cutting to the chase, this is what we want in the custom formula:
=indirect("B"&ROW()) <> indirect("B"&ROW()-1)
The screen now appears thus, confirming that Yes, a difference has been detected:
We ain’t quite Done, as we want to expand the principle to all the columns and rows where we want to detect a change. For us, the columns are (say) B, C, and D.
If you change your range to look as shown here:
… then this is how your grid now appears:
As you can see, there are some wrinkles. I’ll leave you to work those out. You know you want to 🙂