Forum Discussion
How do I conditional format a column if certain value is present in a different column
Hey,
I have a table with my Football Scouts and based on their knowledge they are fit to perform assignments. The assignments are represented as numbers between 1 and 30 and each represent their own column.
Futher I have a column called "Assignment" representing the actual Assigment they are performing. Now I want to highlight every 1 - 30 assigmnent column that is already beingt worked on by a scout; thus present in the column "Assigment"
I tried:
=COUNTIF(AJ$2:AJ$34;E$1:AH$1)>=1
for the range of the table but that doesn't seem to work. It only highlights the first column as shown in the picture.
Also I understand that conditional formatting won't accept Defined Names from tables (if that is what you call them). My table name is Scouts. So AJ$2:AJ$34 is the same as Scouts[Assignment]. At least this works in formulas in cells but does not seem to work for conditional formatting. Is that intended?
Best
Let me clarify your doubts regarding table structural references inside conditional formatting dialog box. It might not seem like they work because you do not get to see Scouts[Assignment] after selecting the whole Assignment column from the actual table. The thing is it only doesn't display the name itself. It shows regular ranges reference but it works like a structural reference - meaning when your table expand the conditional formatting will folow and also expand to next cells. It is simply the way dialog boxes work in Excel - they are a bit buggy and hard to work with. I do not know if this is intended by Microsoft but they seem to be slowly working on updating the funcionalities of dialog boxes.
While the solution suggested by Chat GPT works fine, it could be also written like this:
=COUNTIF($AJ$2:$AJ$34, E$1)>=1
While writing a custom formatting rule you input a formula as if you wanted to write it for the first cell and then drag it down or right.
Kind regards
I solved it with ChatGPT (I am sorry, I should have tried that before posting). Very interesting solution the AI suggested:
=ISNUMBER(MATCH(COLUMN(),$AJ$2:$AJ$34,0))
Worked for me.
Best
