Changing Row Status

Hi there,

My ultimate goal is to capture the "latest" entry from each row. This information will feed into another sheet/Dashboard.

The column names are shown as dates Jan 7, Jan 14, Jan 21 etc... but, are formatted as text/number columns (not date columns).

With that being said, Is there a way to automatically change a specific cell in the status column ex: ryg ball, according to the latest entry which will always be, furthest to the right?

(Screenshot attached)

Hopefully this makes sense.

Help!.png

Find more posts tagged with

Conditional Formatting

Comments

Chris McKay

Hi Lisa,

Based on your layout, I understand that you're looking to populate Row 1 with a summary of the latest data (i.e. the last column) in Rows 2 & 3. Is this correct?

Given that you've got 2 RYG balls below the first row, what criteria are you using to determine the RYG status? Is it possible to calculate the RYG for rows 2 & 3 based on their rightmost values (% and $ amount) and then use these to determine the RYG value for row 1?

The biggest challenge you are going to face is the column based layout you've chosen. Smartsheet does not have any functions that allow you to determine the "rightmost" column beyond a set range. To illustrate what I mean, when you set a range in a formula (e.g. [7-Jan]2:[21-Jan]2), Smartsheet will only look for values between those columns. Unless you pre-create the columns (right up to 30- Dec), Smartsheet will not account for them when you add new columns.

If you do this, then you could set the sheet up this way:

Create a new row (beneath Row 3) and insert this formula in the new row under the first "Date" column:

=IF(OR(LEN([7-Jan]3) > 0,LEN([7-Jan]2) > 0), 1, 0)
Now insert this formula under in the next "Date" column on this new row (i.e. 14-Jan):

=IF(OR(LEN([Column3]2) > 0, LEN([Column3]2) > 0), MAX($[Column2]4:[Column2]4) + 1, "")
Use the handle on the bottom right of the cell to copy it all the way across your row to the last column (i.e. 30-Dec)
If you'd prefer, you can change the font colour to the same as the background and lock the row so it's protected and hidden from view
Now populate the column you want to display the rightmost column value (from Row 3) with this formula:

=INDEX([7-Jan]:[30-Dec], 3, MATCH(MAX([7-Jan]4:[30-Dec]4), [7-Jan]4:[30-Dec]4, 0))
Do the same with Row 2 and this formula:

=INDEX([7-Jan]:[30-Dec], 2, MATCH(MAX([7-Jan]4:[30-Dec]4), [7-Jan]4:[30-Dec]4, 0))

Now this only pulls the most recent values. As I indicated above, I'm not sure what you'd like to do with them. As an example, you could wrap the 2 formulas above in another formula to work out the average or compare them against a threshold:

=IF(INDEX([7-Jan]:[30-Dec], 3, MATCH(MAX([7-Jan]4:[30-Dec]4), [7-Jan]4:[30-Dec]4, 0)) >0.5, "Red", "Green")

Kind regards,

Chris McKay

lisarae723

Wow! Thank you for the in depth response. I greatly appreciate this. It seemed as though it would have been a simple thing to do but, I've been struggling with it. (I am new to smartsheet)

I am not comparing one row to another. Basically each row has its own "Weekly Goal" & the status column will change according to the current numbers/text entered from the right. Do you think I should have set it up in another format/layout?

Chris McKay

Hi Lisa,

My pleasure. The way you'd normally approach (usually in Excel) is to use what's called an Array formula. Really cool, really powerful... but generally not well supported (or at all) in cloud applications like Smartsheet or even Excel Online. So don't fret about it being more difficult than you expected.

I also wouldn't worry too much about what is right or wrong in terms of a layout. The format you've chosen is fine if it works for you. The instructions I posted will allow you to do exactly as you've described for each row. That is, match the numbers to the right against the Weekly Goal criteria and then populate a Status field.

Kind regards,

Chris McKay

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.