Pull in first, second, third, and fourth non-blank values in sequential order
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
Answers
-
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
Adrian Mandile
CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
Collaborative | Holistic | Effective | Systems | Solutions
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives