Cross Reference Sheet with Drop Down so I can use Conditional formatting

Syed Muhafzal

HI there,

So I have a reference sheet, listing down all Australian Public Holidays by State (multiple drow-down).

What I would like to do is to identify these holidays in my Destination sheet, and display if the holiday is either QLD, VIC or NSW or all three.

I am currently using a helper column to identify if the dates are on weekends, and I have placed conditional formatting to the entire row, if it is. If possible, I would like to use the same Day helper column, by showing VIC, or QLD etc. This is so I can use the conditional formatting to colour code the row accordingly.

PS : both dates value are in dates field.

Any suggestions?

SoS | Dan Palenchar

Hello @Syed Muhafzal,

You can pull the State column from your first Sheet in to the second with a lookup formula based on the Holiday and Date fields. From there, you could conditional format based on the State columns having the values you are looking for.

Would that work?

Syed Muhafzal

Thanks Dan. That gave me an idea of using INDEX and MATCH. I created another helper column but that's fine as I can hide them.

=IFERROR(INDEX({State}, MATCH(Date@row, {2024/2025 Public Holidays Date}, 0)), "")

Cant believe I didnt think of it earlier :)

Syed