Make symbols turn Gray if date is in the past but only if that column had an entry

Options

I have a selection of vehicles that have the below formula which will insert the red symbol if they are listed in the corresponding column:

=IF([日付 Date]@row < TODAY(), "Gray", IF(CONTAINS("4号室 Room 4", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting]), "Red"))

What I need to know is how to ensure that once the row is no longer 'todays' entry, the symbol needs to remain but turn gray (to show a history of what specific vehicles had been labeled Red). Currently, every column for every vehicle turns gray once it is no longer today, even if the [問題報告RV東照明 Reporting Issue RVE Lighting] column does not mention that vehicle.

I tried my best to explain this in my screenshot, please let me know if you need clarification.


Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @CRUSJ

    You can add another IF statement at the front of your formula that first checks the cell for this row to see if it has the relevant selection.

    =IF(HAS([問題報告RV東照明 Reporting Issue RVE Lighting]@row, "4号室 Room 4"), IF(AND([日付 Date]@row < TODAY(), CONTAINS("4号室 Room 4", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Gray", IF(CONTAINS("4号室 Room 4", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting]), "Red")))


    Cheers,

    Genevieve

Answers

  • Aaron Manley
    Aaron Manley Overachievers
    Options

    Couldn't you update your column formula for your symbol columns so that the Gray only if Red was present before? I'm mainly going off your formula as an example.

    Your Formula:

    =IF([日付 Date]@row < TODAY(), "Gray", IF(CONTAINS("4号室 Room 4", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting]), "Red"))

    Updated Formula:

    =IF(AND([日付 Date]@row < TODAY(), CONTAINS("4号室 Room 4", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Gray", IF(CONTAINS("4号室 Room 4", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting]), "Red"))

    This formula would change the symbol to Red if the value being searched for was found, and then would turn it Grey if the date is not Today. This may not be what you're after, but because the symbol columns are column formulas I don't see why this wouldn't work for you.

  • CRUSJ
    CRUSJ ✭✭
    Options

    Hi Aaron, thank you for your reply. This formula is really close to working! However, it is adding the colored dots to the entire column, even when one of the vehicles is not selected in that row. See my screenshot. I selected vehicle 1 on the lower 2 rows as having an issue on a PAST date so those should be grey and the two upper rows should have no circle at all because vehicle 1 was not selected for those upper 2 rows. The color changing is correct, but its filling the entire column with circles rather than the individual rows.


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @CRUSJ

    You can add another IF statement at the front of your formula that first checks the cell for this row to see if it has the relevant selection.

    =IF(HAS([問題報告RV東照明 Reporting Issue RVE Lighting]@row, "4号室 Room 4"), IF(AND([日付 Date]@row < TODAY(), CONTAINS("4号室 Room 4", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting])), "Gray", IF(CONTAINS("4号室 Room 4", [問題報告RV東照明 Reporting Issue RVE Lighting]:[問題報告RV東照明 Reporting Issue RVE Lighting]), "Red")))


    Cheers,

    Genevieve

  • CRUSJ
    CRUSJ ✭✭
    Options

    @Genevieve P.

    Thank you!!! This did the trick! I had gone down an absolute rabbit hole of formulas and learned alot but ultimately couldn't find quite the right formula. You're a life saver!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem at all!

    Team effort with @Aaron Manley 😎

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!