Conditionally formatting cells based on a row cell and a column header

Question

I have a sheet of dates and generic numbers something like this:

            Winner  2  4  7  12  23  54  77
Date 1        4        X
Date 2        23                  X
Date 3        12             X

I have figured out how to do conditional formatting to highlight the cell where the X is, buy putting a formula into every cell. What I am looking for is a mass formula that will look a the Winner cell and then match to the column header and highlight the appropriate cell.

Is this possible?

pnuts · Accepted Answer · 2015-11-10 18:41:57Z

1

Not only possible but with a delightfully easy formula, given your layout. Please try selecting the relevant range (if Winner is in B1, something like B2:I100), Format - Conditional formatting..., Custom formula is and:

=$B2=C$1

with formatting of your choice and Done.

This does no rely on the cells containing an X - they could be empty.

edited Nov 10, 2015 at 18:41

answered Nov 10, 2015 at 18:35

pnuts

59.7k11 gold badges92 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rtindru Over a year ago

Is it correct to say this breaks if the columns are re-ordered?

Collectives™ on Stack Overflow

Conditionally formatting cells based on a row cell and a column header

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related