INDEX/COLLECT #INCORRECT ARGUMENT error
I have been hitting a wall with an INDEX/COLLECT formula that operates within the confines of a single sheet (no cross sheet formulas). For background on the sheet we use it to track results from various tests performed on a product at various stages. What I am hoping to have the formula do is identify any time a test has been positive on previous tests and collect that into a single column for easy review in a report.
So for example, this is what I would want to see in the previous positives column based on the results of earlier tests:
I have been getting an #INCORRECT ARGUMENT error with every step of the formula, even when I break it down to the most simple version of it without a ton of varying criterion.
The current formula with the least number of criterion reads like this: =INDEX(COLLECT([Test #]:[Test #], [Batch #]:[Batch #], [Batch Number]@row))
This is obviously a much more simplified version of the sheet, so I can add in screenshots of the actual sheet. But hoping the piece I am missing is glaringly obvious to others!
Best Answer
-
First you are going to want to insert a column that combines the Batch and Test numbers into a single string.
=[Batch #]@row + "-" + [Test #]@row
Finally the formula you are going to wan to use is going to look like this...
=JOIN(COLLECT([Helper Column]:[Helper Column], [Batch #]:[Batch #], @cell = [Batch #]@row, [Test #]:[Test #], @cell< [Test #]@row, Result:Result, @cell = "positive"), ", ")
Answers
-
First you are going to want to insert a column that combines the Batch and Test numbers into a single string.
=[Batch #]@row + "-" + [Test #]@row
Finally the formula you are going to wan to use is going to look like this...
=JOIN(COLLECT([Helper Column]:[Helper Column], [Batch #]:[Batch #], @cell = [Batch #]@row, [Test #]:[Test #], @cell< [Test #]@row, Result:Result, @cell = "positive"), ", ")
-
@Paul Newcome thank you! You always have the answer I need. Appreciate the help.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 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
Check out the Formula Handbook template!