SOLVED

Highlight cells based on public holidays for different states

Copper Contributor

Greetings everyone, 

I'm trying to highlight cells in columns A:H if they fall on public holiday dates mentioned in the cells J2:L18. Now what if the public holidays differ according to different states? Ex: If we highlight the columns based on state – VIC’s public holidays, and if state NSW’s date fall on the same date but it is not a public holiday, how do we highlight cells based on different state’s holidays without disturbing other states' normal days?

Please refer to the formula I have used. 

Thanks in a

zaidk4496_0-1675054659020.png

 

 

zaidk4496_1-1675054341102.png

 

 

2 Replies
best response confirmed by zaidk4496 (Copper Contributor)
Solution

@zaidk4496 I would start by assigning a name to each range of state holidays, using the abbreviations used in column B.

 

Then the following rule, applied to columns A:H will highlight the row if the date in A is a holiday in state B:

 

=SUM(IFERROR(FIND($A2,INDIRECT($B2)),0))

 

Please find a working example attached.

 

 

@Riny_van_Eekelen Got it. 

You're a legend. 

Thanks

1 best response

Accepted Solutions
best response confirmed by zaidk4496 (Copper Contributor)
Solution

@zaidk4496 I would start by assigning a name to each range of state holidays, using the abbreviations used in column B.

 

Then the following rule, applied to columns A:H will highlight the row if the date in A is a holiday in state B:

 

=SUM(IFERROR(FIND($A2,INDIRECT($B2)),0))

 

Please find a working example attached.

 

 

View solution in original post