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

Answers

  • JCluff
    JCluff ✭✭✭✭

    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)))))), "")

  • Renita Luck
    Renita Luck ✭✭✭
    Answer ✓

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!