Join/Collect with Has/Not Has functions
Hello,
I'm stumped on this formula and need assistance please! I am trying to populate Courses to Approve -Dean column based on the choice in the PC or Chair Approval Column. It works until I get to the "Some Courses Denied" choice in the PC or Chair Approval Column - which then is supposed to be taking the courses listed in the All Courses Requested column and removing the courses listed in the Courses Declined - PC or Chair column and placing the result in the Courses to Approve- Dean column.
This is the formula I have so far (please note that {Course Name} references another sheet where I have joined the Subj/No with the Course Title into one field and used that column as the range (Course Name) - which results in the full course name i.e. ACC:100 Applied Accounting. - In the image below the red arrows show what should NOT be included in the Courses to approve column.
=IFERROR(IF([PC or Chair Approval]@row = "All Courses Denied", "ALL COURSES DENIED", IF([PC or Chair Approval]@row = "", [All Courses Requested]@row, IF(AND([PC or Chair Approval]@row <> "", [PC or Chair Approval]@row = "All Courses Approved"), [All Courses Requested]@row, IF([PC or Chair Approval]@row <> "", JOIN(COLLECT({Course Name}, {Course Name}, HAS([All Courses Requested]@row, @cell), {Course Name}, NOT(HAS([Courses Declined - PC or Chair]@row, @cell))), CHAR(10)))))), "")
Thanks in advance for any assistance!
Renita
Best Answer
-
Thank you!
Answers
-
Hi @Renita Luck,
It looks like your HAS() formula is reversed. It should be range then criteria, you wrote it as criteria then range.
Try the formula below:
=IFERROR(IF([PC or Chair Approval]@row = "All Courses Denied", "ALL COURSES DENIED", IF([PC or Chair Approval]@row = "", [All Courses Requested]@row, IF(AND([PC or Chair Approval]@row <> "", [PC or Chair Approval]@row = "All Courses Approved"), [All Courses Requested]@row, IF([PC or Chair Approval]@row <> "", JOIN(COLLECT({Course Name}, {Course Name}, HAS(@cell, [All Courses Requested]@row), {Course Name}, NOT(HAS(@cell,[Courses Declined - PC or Chair]@row))), CHAR(10)))))), "")
-
Thank you!
-
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!