Pull in first, second, third, and fourth non-blank values in sequential order

ingak

Hi there, I'm trying to set up a smartsheets form with tax amounts so that I can have column tax amount 1 will pull in the first non-blank value, tax amount 2 second, etc. I figured out the formula for the first cell, but struggling for the others. Any help is much appreciated!

Formula i used for the first column: INDEX(COLLECT(GST@row:QST@row, GST@row:QST@row, @cell <> 0), 1, 1).

As with the example below, Tax Amount 3 column should be blank because Tax Amount 1 is already picking up that amount. I've been playing around with the IF formula but it's getting kind of clunky and complicated.

Thanks again in advance!

Inga

Adrian Mandile CHESS

Hi @ingak,

You don't need the second ",1" (for row/column) in the INDEX() formula because you will only ever have a single-dimension array returned by your COLLECT() function, because you're using @row.

You'd need to add an IFERROR() to the formula because you won't know how many Tax Amounts you'll have in the returned INDEX() array (i.e. between 0 to 4 amounts).

I'd also consider adding absolute addressing ('$') to the column names so that you can easily drag the formula across the 4 Tax columns, then edit the last Index value. So enter:

=IFERROR(INDEX(COLLECT($GST@row:$QST@row, $GST@row:$QST@row, @cell <> 0), 1),"") for Tax Amount 1

Drag the formula across the other 3 columns, and edit the INDEX() row/column value to be:

=IFERROR(INDEX(COLLECT($GST@row:$QST@row, $GST@row:$QST@row, @cell <> 0), 2),"") for Tax Amount 2

=IFERROR(INDEX(COLLECT($GST@row:$QST@row, $GST@row:$QST@row, @cell <> 0), 3),"") for Tax Amount 3

=IFERROR(INDEX(COLLECT($GST@row:$QST@row, $GST@row:$QST@row, @cell <> 0), 4),"") for Tax Amount 4