Incorrect Argument Set when using HAS in an INDEX(COLLECT
Has anyone had much success using a HAS in an INDEX COLLECT?
I'm trying to get something like this to work:
=INDEX(COLLECT({ITEM ID}, {TONER COLOR}, [Toner Color]@row, HAS({MODEL}, [Xerox Model]@row), 1))
In one sheet I have the following columns:
- ITEM ID
- TONER COLOR
- MODEL
The models have multi value selects. Look at this:
In another sheet I am collecting the following data:
- Model #
- Toner Color
And I want to RETURN the item ID. However, as you may see above, there's a lot of model numbers in one field in a multi-select. I prefer to do it this way as the ITEM ID is repeated for 20 models.
I tried searching and I'm having issues with HAS and it returns INVALID ARGUMENT SENT
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
Best Answer
-
Try this one:
=INDEX(COLLECT({ITEM ID}, {TONER COLOR}, [Toner Color]@row, {MODEL}, HAS(@cell, [Xerox Model]@row)), 1)
Answers
-
Try this one:
=INDEX(COLLECT({ITEM ID}, {TONER COLOR}, [Toner Color]@row, {MODEL}, HAS(@cell, [Xerox Model]@row)), 1)
-
YESSS! So I reversed the HAS! Thank you!
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
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!