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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!