Forum Discussion
Help with conditional formatting
Will the names in column A be the same each month, in the same order? If so:
Select C2:AQ99 on the Nov23 sheet.
C2 should be the active cell in the selection.
On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than or equal to' from the second drop down.
In the box next to it, enter the formula
=Oct23!B2
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.
Repeat these steps, but with 'equal to', =Oct23!B2-1 and light red.
Repeat them again, with 'equal to', =Oct23!B2+1 and dark green.
Finally, with 'greater than or equal to', =Oct23!B2+2 and light green.
But would the formulas be different if the list of names changed? It's a game so I'll be buying and selling players.
The spreadsheet is a stat tracker to see if my players are improving or declining.
If the names in column A change from month to month, the formulas in the rules become more complicated. In each of the rules, replace Oct23!B2 with
INDEX(Oct23!B:B, MATCH($A2, Oct23!$A:$A. 0))
or with
XLOOKUP($A2, Oct23!$A:$A, Oct23!B:B)
- When you get time please may I ask for the formula to be wrote out like the first time please?
So I know which one is +1 (Dark green), +2 (Light Green), -1 (Light red), -2 (Red).
I'm totally new to excel & have learning difficulties, your first post was perfect for me and made total sense, I looked all over for help and your the first to explain the way I understand. Thank youHere is my first reply, modified:
Will the names in column A be the same each month, in the same order? If so:
Select C2:AQ99 on the Nov23 sheet.
C2 should be the active cell in the selection.
On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than or equal to' from the second drop down.
In the box next to it, enter the formula=XLOOKUP($A2, Oct23!$A:$A, Oct23!B:B)
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.Repeat these steps, but with 'equal to', =XLOOKUP($A2, Oct23!$A:$A, Oct23!B:B)-1 and light red.
Repeat them again, with 'equal to', =XLOOKUP($A2, Oct23!$A:$A, Oct23!B:B)+1 and dark green.
Finally, with 'greater than or equal to', =XLOOKUP($A2, Oct23!$A:$A, Oct23!B:B)+2 and light green.