Changing Row Status

lisarae723

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. 

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:

1. 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)
2. 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, "")
3. 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)
4. 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
5. 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)) 
6. 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