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.

Find more posts tagged with

Accepted answers

Paul Newcome

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

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))

All comments

Paul Newcome

Are all of the columns next to each other, and are you going to be putting this formula on the same sheet or a different sheet from the multiple columns?

Nathan Grant

Hi Paul. The columns aren't next to each other. There's a date column in between each of them. I'll probably use the same formula to find the latest date as well after we get this one working.

The formula and "Latest Site Visit Notes" column are in the same sheet.

Paul Newcome

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

That worked perfectly.

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

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

Paul Newcome

Glad you were able to get it working. Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.