Last value of a row that is not empty

Hello everyone,

I have to make a milestone tracker that I update week by week by adding a new column (the rows are the projects). I can't just modify a column because they require me to keep a history of the data. columns (date type) can contain a value or be empty. I need to get the last updated date for each project (not necessarily this value will be the maximum of the array).

For example, I would have an array of columns with information [" ", 6, 3, " ", 1, 2, " "] and I would need to extract the value of -2- in an additional column (for this example).

I've tried doing a reverse if-else tree (starting from the last value to the first) looking for the last non-empty column, but it's not scalable.

I would appreciate any help or suggestion. It is important to clarify that SmartSheets do not have loop functions (while() or for()) and there is no Hloopup() either.

Thanks in advance,

Find more posts tagged with

Sheets

Formulas

Solution Center

Conditional Formatting

Project Management

Accepted answers

Jason Tarpinian

I wanted to give me shot at this one, as it seemed like a nice challenge! To summarize what my solution does, it will take the farthest right-most non-blank cell in the range from [Week 1]:[Range Extender]. So if I had been understanding your question, you could add week 9 in between Week 8 and Range Extender per my screenshot below, and if you'd plug a value into week 9, it would return that value instead of 2.

To translate the formula below to regular language, it joins all the non-blank values with a unique character (* in my case), and then it replaces the last instance of * (SUBSTITUTE has the optional [replace_num] function, which can be used to determine the last instances of * using COUNT non-blank cells) with a separate unique character (&). Then you can use the RIGHT(LEN(FIND combo to return the last non blank value.

=RIGHT(SUBSTITUTE(JOIN(COLLECT([Week 1]@row:[Range Extender]@row, [Week 1]@row:[Range Extender]@row, <>""), "*"), "*", "&", COUNT([Week 1]@row:[Range Extender]@row) - 1), LEN(SUBSTITUTE(JOIN(COLLECT([Week 1]@row:[Range Extender]@row, [Week 1]@row:[Range Extender]@row, <>""), "*"), "*", "&", COUNT([Week 1]@row:[Range Extender]@row) - 1)) - FIND("&", SUBSTITUTE(JOIN(COLLECT([Week 1]@row:[Range Extender]@row, [Week 1]@row:[Range Extender]@row, <>""), "*"), "*", "&", COUNT([Week 1]@row:[Range Extender]@row) - 1)))

All comments

Paul Newcome

I would suggest a helper row where you can put numbers across in ascending order from left to right. This will allow us to figure out what the highest number is that has data in it on each row.

1, 2, 3, 4, 5, etc.

From there you would insert a placeholder column on the far right of the sheet Put the number zero in the helper row of this column). You want to insert any new columns for data entry to the LEFT of this placeholder column. This will allow you to automatically include new columns in the formula so that you don't have to manually adjust the ranges whenever new columns are added.

Then you can use something like this:

=INDEX([First Column]@row:[Placeholder Column]@row, 1, MAX(COLLECT([First Column]$1:[Placeholder Column]$1, [First Column]@row:[Placeholder Column]@row, @cell <> "")))

If you do not want to use the helper row, then you would need to enter something in the blank columns such as "N/A" or "-" or anything else really so that the cell is not blank.

From there you would still need the placeholder column (this can be hidden in both options once it is set up) and your formula would look like this:

=INDEX([First Column]@row:[Placeholder Column]@row, 1, COUNTIFS([First Column]@row:[Placeholder Column]@row, @cell <> ""))

Jason Tarpinian

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.