Changing an older cell based on criteria from a newer cell

ChrisWolfeENG · 04/30/21

Hello,

Looking for a little assistance with a formula (or workflow), if even at all possible.

I have a row of data with a Date, a Room and a Status of “Conditional Pass”.

When that same Room gets checked again at a later Date, has a Status of “Pass” and a new row is added, is it possible to change the original Status entry to something else, like “Pass – UPDATE”.

Screenshots are attached to help illustrate what I am going for.

Any and all assistance is greatly appreciated!

Change Cell_1.PNG

Change Cell_2.PNG

James Keuning · 05/01/21

You would use another sheet (sheetB) to contain the dated status records, and use your main sheet (sheetA) to collect information from sheetB.

You use a combination of Match, Index, Collect, and Max on your main sheet to look to your status record sheet.

Basically, sheetB will have a running list of dated statuses. sheetA has a record per conference room. A formula in sheetA looks at sheetB and says, for the conference room on this row, go look at sheetB and gather up all of the Statuses and the Dates for those statuses and find the status with the latest date, then return that status in this field.

There are probably more examples, but here is one application:

https://community.smartsheet.com/discussion/71500/collect-and-max

Mark Cronk · 05/01/21

Hi @ChrisWolfeENG ,

You can't change a old record based on a new record in the way you're asking. You could add a new column [current status] with the column formula:

=INDEX(COLLECT([overall status]:[overall status], date:date, MAX(COLLECT(date:date, room:room, room@row)), room:room, room@row))

Work?

Mark

Mark Cronk · 05/27/21

Chris, Appreciate the update. Exciting. As long as it works it's a great solution. Well done. Thank you for contributing to the Community.

Mark

James Keuning · 05/01/21

You would use another sheet (sheetB) to contain the dated status records, and use your main sheet (sheetA) to collect information from sheetB.

You use a combination of Match, Index, Collect, and Max on your main sheet to look to your status record sheet.

Basically, sheetB will have a running list of dated statuses. sheetA has a record per conference room. A formula in sheetA looks at sheetB and says, for the conference room on this row, go look at sheetB and gather up all of the Statuses and the Dates for those statuses and find the status with the latest date, then return that status in this field.

There are probably more examples, but here is one application:

https://community.smartsheet.com/discussion/71500/collect-and-max

Mark Cronk · 05/01/21

Hi @ChrisWolfeENG ,

You can't change a old record based on a new record in the way you're asking. You could add a new column [current status] with the column formula:

=INDEX(COLLECT([overall status]:[overall status], date:date, MAX(COLLECT(date:date, room:room, room@row)), room:room, room@row))

Work?

Mark

ChrisWolfeENG · 05/04/21

Hello @James Keuning & @Mark Cronk ,

I think I understand the aspect of having another sheetB (kinda like a truth-table) and referencing that sheet for data in sheetA.

I will try the formula as well and see if it works as intended.

Thank you for the responses! Your expertise is greatly appreciated.

ChrisWolfeENG · 05/27/21

Hey @Mark Cronk ,

It has been a while, but I wanted to drop an update after I finally got it working.

I have a Latest Date helper column with the formula:

=MAX(COLLECT(Date:Date, Room:Room, Room@row))

...which takes all of the Dates, compares that to the same Room Names, and enters the maximum Date in the cell.

Then I have another helper column with the test:

=IF([Latest Date]@row = Date@row, "True", "False")

...which will compare the Latest Date to the Date the record was entered, if they are the same, indicate "True", if not then indicate "False".

Then I built a workflow around anything with a "False" to move to an archive.

As a complete novice with formulas and functions there probably is a better, cooler, cleaner way of doing this....but it works!

Thank you again for your time and expertise assisting with this. By building my own formulas from scratch, I have a much better understanding now. +10 Insightfuls to you and James!

Health and happiness to you and yours.

Best Regards,

Chris

Mark Cronk · 05/27/21

Chris, Appreciate the update. Exciting. As long as it works it's a great solution. Well done. Thank you for contributing to the Community.

Mark

Changing an older cell based on criteria from a newer cell

Best Answers

Answers

Help Article Resources

Categories