I’m trying to add a function to my finance spreadsheet.
I have the items on the left and one column for each day of the year and at the bottom, I have 3 rows; ‘IN’, ‘OUT’ & ‘TOTAL’.
What I want to do is have the ‘TOTAL’ cell check to see if it’s less than the following days ‘OUT’ cell and if it is, turn the ‘TOTAL’ cell red.
But isn’t that just checking on cell C6, what about cell B6 & D6 ?
I’ve tried the following, but it does not produce the desired result.
I’m figuring it all wrong.
I want the check to be on the ‘IN’ & ‘OUT’ of the same colunm.
But the thing which influences the ‘IN’ is the ‘TOTAL’ of the previous colunm as they are added together, so if the ‘TOTAL’ is not enough, I need to work out some adjustments to perform on the stuff going out.
Addressing your question about C6: No, that is not just for C6. Here is why:
When you use a simple cell reference in conditional formatting, it encodes the move relative to the top left corner of the conditionally formatted cell range. That move is then “replayed” for each cell in the formatted cell range. So, since @LeroyG marked the 3-cell range for formatting then used C6 in the format, that was relative to B7. That means each cell in the 3-cell range will be looking up one right one, the same “move” as from B7 to C6.
@joshua4 Thanks for the explanation as to how it works as this is all new to me, though it did to a basic IT course covering the basics… Waaaay back in 1992, I don’t recall anything like this (I blame old age 
). I will try again.
@LeroyG Thanks for the help and solution. It took a little while for me to understand it. Many thanks.
1 Like