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

CRUSJ · 09/25/22

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.

Genevieve P. · 10/03/22

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

manley85am · 09/26/22

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 · 09/27/22

https://community.smartsheet.com/discussion/comment/344766#Comment_344766

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. · 10/03/22

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 · 10/03/22

@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. · 10/03/22

No problem at all!

Team effort with @Aaron Manley 😎

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

Best Answer

Answers

Help Article Resources

Categories