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

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?

Find more posts tagged with

Formulas

dates

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.