Using an OR in COLLECT
Hello -
I'm trying to use an OR operator in the criteria of a collect function. This is currently returning an #INCORRECT ARGUMENT SET:
=IFERROR(INDEX(COLLECT({WAP Deposit Number}, {WAP Date}, Date147, {WAP Type}, OR("CHECK", "CREDIT"), 1), ""))
I'm trying to return the a deposit number {WAP Deposit Number} if the Date matches and the type is EITHER "Check" or "Credit"
It's probably something really dumb I'm missing...
Any ideas?
Thanks,
Andy
Comments
-
Hi Andy,
This might help!
https://help.smartsheet.com/function/or
Did it help?
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andree -
Thanks - Unfortunately, the syntax is a bit different in the collect function.
It's COLLECT(RANGE, Criterion_range1, criterion1, etc.)
-
Happy to help!
I know that, but I didn't have time to write it out before.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I was just looking at this again, and I realized that your parenthesis are also just a little off..
.
Here is your original formula:
=IFERROR(INDEX(COLLECT({WAP Deposit Number}, {WAP Date}, Date147, {WAP Type}, OR("CHECK", "CREDIT"), 1), ""))
.
The parenthesis after "CREDIT" is closing out the OR.
This means that the 1 is actually included in the COLLECT statement (as a range which doesn't work and also happens to be missing a criteria). This pushes "" to your row number in your INDEX function which means that nothing is specified for the second portion of your IFERROR statement.
All of these things on their own will cause the #INCORRECT ARGUMENT SET error.
Add in a closing parenthesis after "CREDIT", and remove one form the end of the formula.
This along with the @cell references in the OR function should clear everything up.
=IFERROR(INDEX(COLLECT({WAP Deposit Number}, {WAP Date}, Date147, {WAP Type}, OR(@cell = "CHECK", @cell = "CREDIT")), 1), "")
-
EDIT to above (cloudfare blocking issues again):
I also wanted to apologize for missing this earlier. I was so focused on the title of your post trying to use an OR within a COLLECT that I didn't look at the formula as a whole in reference to the error message you were getting.
Let us know if this helps.
-
@Paul Newcome I have a very similar formula I am trying to write. I am looking to for an index/Collect formula that will pull in a result for either "Location Status'" of "Location Recommended" or "Location Approved". Where did I screw this up?
=IFERROR(INDEX(COLLECT({Master City Name}, {Location Status}, "Location Recommended", OR({Location Status}, "Location Approved")), 1), "")
-
@Ross Novotny Try this one...
=IFERROR(INDEX(COLLECT({Master City Name}, {Location Status}, OR(@cell = "Location Recommended", @cell = "Location Approved")), 1), "")
-
@Paul Newcome & @Andrée Starå - Just wanted to thank you for helping so much in these forums! Yet again you gave me an answer. <3
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!