Anyone else experiencing formulas for the RYG column randomly going haywire?

Michele Phillips

I notice the project summaries on a couple of my landing pages went sideways. It appears the RYG column to the left is the culprit. What happens is my R, Y, G, GR columns out to the right will appear as INVALID as opposed to populating the totals. After some troubleshooting I realized the only thing that works to correct it is to reset the RYG column formula to the left, and the individual R, Y, G, GR columns out to the right will then populate correctly. This is what drives the project summary widget on the on the landing page. By reset I mean copying the RYG formula on the left from the top row, deleting it, saving, and then pasting it back in and pulling it down through all the rows. We've had these sheets set up forever, and I experienced the same issue on an entirely different project sheet about a month ago. Any idea? Is this a Smartsheet blip? Smartsheet doing upgrades, or bug fixes that might affect the behavior? I didn't take a screen shot prior, when INVALID showed up in the count columns to the right, but this is the correct look, since I corrected it. I also attached a snippet of the project summary from the landing page, which is reflected correctly. This morning, prior to my fixing the sheet issue, the widget showed as no data source. Thank you!

Michele

Sameer Karkhanis

Can you please share the formulae used in the columns to debug?

Michele Phillips

We use the exact same formula on all 12 of our project sheets. The issue mentioned above has only affected 2 sheets, and in only the past month, which makes me wonder what could be going on all of a sudden.

Formula for the RYG column to the left of the sheet:

=IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([Start Date]@row > TODAY(), "Gray", IF([% Complete]@row > 0.8, "Green", "Yellow"))))

Thanks so much.

Michele

SmartLew

I think I had a similar issue before ; Invalid error in this scenario usually happens when a scenario that isn't included in your IF statements e.g a blank date or a number that is out of range. Then there is no R/Y/G populated in the main column, and therefore nothing for the calculation columns to look at.

Could it be that during the fixes you made, a date that was previously missing was added, or a number changed?

Maybe have a lool at editing your formula to cover all bases.

Michele Phillips

Hi there -

I appreciate your suggestion and reply. Logical, but I think that should only affect the RYG for that row ------ IF([End Date]@row --- based on my formula above. Makes sense in theory, but then how is the entire sheet affected? Like I mentioned, this has only happened twice since 2019, but ironically both times have been in the past month, affecting two completely different sheets. I can chalk it up to a blip, but when I start seeing patterns I become concerned, not only with this, but how reliable the data is across the board. At this point I will wait and see if it happens again, at which time I will bring the concern up to our rep.

Thanks so much!