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?



  • SoS | Dan Palenchar
    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?

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • Syed Muhafzal
    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 :)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!