Return rightmost non-blank cell value in a row?

Jeff Jensen (LFS)

Hello everyone,

I created a more roundabout solution for this involving a separate grid, but I'm wondering if there is an elegant formula that would do the following.

See first screenshot below. Without getting into too much detail, I am hoping for a formula to put into [2022 YTD]13 that would look at the values in [Q1 2022]13:[Q4 2022]13, and return the rightmost, non-blank value (i.e. the most recent value).

Normally I would have the LARGE function pick the largest value, since the value of most of such data I collect increases in number over time. However, since the numbers reported here will be percentages, it is entirely possible that the most recent number could decrease from the previous quarter, so LARGE is not an option.

I was able to do this using a separate grid that put everything into a column/vertical format, then using actual calendar dates, MAX(COLLECT) and INDEX(COLLECT) functions, and using a lot of cell linking to get the result I wanted then return that value to [2022 YTD]13. The two next screenshots show those formulas on that separate grid:

But there has to be a more elegant solution that keeps everything to one row in one grid, as specified at the beginning. Any ideas? Or is this simply not possible?

Thanks in advance!

Genevieve P.

Hi @Jeff Jensen (LFS)

Smartsheet doesn't currently have a function that can look at all the columns in a sheet and return the one furthest-non-blank-to-the-right or furthest-non-blank-to-the-left. Please submit your feedback and request to our Product team through this form, letting them know of your specific use-case!

I'd be interested to see if other Community members have suggestions, but I actually think the longer route that you have currently set up may be the best way to go. The only other thing I can think of would be a very long nested-IF statement which checks each cell, starting on the very far right, to see if it's blank and returns its value if it isn't.

Cheers,

Genevieve

Genevieve P.

Hi @Jeff Jensen (LFS)

Smartsheet doesn't currently have a function that can look at all the columns in a sheet and return the one furthest-non-blank-to-the-right or furthest-non-blank-to-the-left. Please submit your feedback and request to our Product team through this form, letting them know of your specific use-case!

I'd be interested to see if other Community members have suggestions, but I actually think the longer route that you have currently set up may be the best way to go. The only other thing I can think of would be a very long nested-IF statement which checks each cell, starting on the very far right, to see if it's blank and returns its value if it isn't.

Cheers,

Genevieve

Jeff Jensen (LFS)

Thanks Genevieve! I figured it wasn't currently possible but was hopeful. I'll consider submitting this to SmartSheet's Product Team then. Very much appreciate your time on this.

Leibel S

@Jeff Jensen (LFS)

Not exactly sure of your situation but a general formula that should work for you is:

=INDEX(COLLECT([Q1 2022]13:[Q4 2022]13,[Q1 2022]13:[Q4 2022]13,@cell<>""),1,count([Q1 2022]13:[Q4 2022]13))

Jeff Jensen (LFS)

Hi Leibel - thank you so much for your input. I see where you are going with this. However that formula gives me an #INVALID VALUE error. It only works if any one (and only one) of the four cells has a value in it. Through a lot of trial and error, the best I can figure is the COLLECT function doesn't like it when multiple columns are specified for the range.

For example, I simplified the formula so it checks columns rather than a range of cells in a single row:

=INDEX(COLLECT([Q3 2022]:[Q3 2022], [Q2 2022]:[Q2 2022], "asdf"), 1)

This formula collects the values in the “Q3 2022” column for rows where the value in the “Q2 2022” column is "asdf", and returns the first entry in that collection. This works just fine:

The formula still works if I restrict the COLLECT range and COLLECT criterion_range1 to the same cell range in the same columns. For example, =INDEX(COLLECT([Q3 2022]12:[Q3 2022]16, [Q2 2022]12:[Q2 2022]16, "asdf"), 2) does return 6.345.

However, once I introduce multiple columns to the COLLECT range, I get an #INCORRECT ARGUMENT SET error. Below I expanded the range to Q2 2022 through Q1 2023, and assuming I wrote the formula correctly it should return the first value in the first column of collected values, but gives me the #INCORRECT ARGUMENT SET error instead:

=INDEX(COLLECT([Q3 2022]12:[Q1 2023]16, [Q2 2022]12:[Q2 2022]16, "asdf"), 1, 1)

And it doesn't matter if I specify individual cells or entire columns for the COLLECT range.

Certainly I could be doing something wrong, but I've tried lots of formula variants, so it appears the issue is the COLLECT range simply does not accept multiple columns. Thanks again for your help!

Leibel S

@Jeff Jensen (LFS)

You just need to wrap it in an IFERROR function.

This will remove any issues of blank rows...