Inspect Cells Until Contents Found, Then Copy to Another Cell

I'm trying to create a column ("Latest Site Visit Notes") on my sheet that fills in with the latest site visit notes. We have existing columns for "Construction Supervisor Visit 1 Notes" thru "Construction Supervisor Visit 20 Notes". Our hundreds of projects all have various quantities of site visits performed so far.

I'd like the "Latest Site Visit Notes" column to inspect column 20 for contents, if blank then it'll inspect 19, if blank then inspect 18, if not blank then display that column data in "Latest Site Visit Notes"

Can this be accomplished with a Workflow or just with some cell code? I think I can figure out how to do it with a Workflow but it looks like it'll be huge and possibly resource intensive.

Thanks for the help.

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Actually it may be easier if we pull the latest date first.

    =MAX(COLLECT([First Date]@row:[Last Date]@row, [First Date]@row:[Last Date]@row, ISDATE(@cell))


    Then we use this to look across the row and use an INDEX function to pull the comment from the column immediately to the right of the matching date column.

    =INDEX([First Comment]@row:[Last Comment]@row, 1, MATCH([Max Date Column]@row, [First Date]@row:[Last Date]@row, 0))

  • Nathan Grant
    Answer ✓

    That worked perfectly.

    It took me a moment to understand your instructions at first since I'm new to this. I created a Latest Visit Date column to paste your first code. Then I pasted your second code in the Latest Site Visit Notes column. Then I swapped in the correct column names and it works.

    The Latest Date column I ended up with the following code.

    =MAX(COLLECT([Construction Supervisor Visit 1 Date]@row:[Construction Supervisor Visit 20 Date]@row, [Construction Supervisor Visit 1 Date]@row:[Construction Supervisor Visit 20 Date]@row, ISDATE(@cell)))

    The Latest Notes column I ended up with the following code.

    =INDEX([Construction Supervisor Visit 1 Notes]@row:[Construction Supervisor Visit 20 Notes]@row, 1, MATCH([Construction Supervisor Latest Visit Date]@row, [Construction Supervisor Visit 1 Date]@row:[Construction Supervisor Visit 20 Date]@row, 0))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!