Updating RYGB status based on date AND dropdown selection in multiple columns



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To help avoid having to adjust formulas or miscounting columns etc if one is added or removed, you could use a COUNT function either in a helper column or in the formula itself to automatically count the number of columns for comparison. As long as new columns are added and/or old columns are deleted from in between the the two columns referenced in the formula, it will automatically compare to the correct number. Something along the lines of...


    =COUNTIFS([First Column Name]@row:[Last Column Name]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))


    This will give you a count of all cells that are both blank and not blank within the specified range. So in this particular case...


    In a helper column:

    =IF(COUNTIF(Barry@row:Dianne@row, OR(@cell = "Done", @cell = "N/A")) = [Count Helper]@row, "Green", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", "Blue"))


    In the formula itself:

    =IF(COUNTIF(Barry@row:Dianne@row, OR(@cell = "Done", @cell = "N/A")) = COUNTIFS(Barry@row:Dianne@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell)))), OR(ISBLANK(@cell), NOT(ISBLANK(@cell)))), "Green", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", "Blue"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!