Changing an older cell based on criteria from a newer cell

04/30/21
Accepted

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!


Best Answers

  • James KeuningJames Keuning ✭✭✭
    Accepted Answer

    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:


  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    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, [email protected])), room:room, [email protected]))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • James KeuningJames Keuning ✭✭✭
    Accepted Answer

    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:


  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    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, [email protected])), room:room, [email protected]))

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • edited 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.

  • 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, [email protected]))

    ...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 = [email protected], "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 CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Sign In or Register to comment.