Forum Discussion
Highlight cells based on public holidays for different states
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 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.
2 Replies
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.
