Help with Join/Collect and Has and Not/Has formula

Renita Luck

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.

IFERROR(IF([PC or Chair Approval]@row="All Courses Denied", "ALL COURSES DENIED", IF[PC or Chair]@row="", [All Courses Requested]@row, IF(AND([PC or Chair]@row<>"", [PC or Chair Approval]@row="All Courses Approved"), [All Courses Requested]@row, IF([PC or Chair]@row="Some Courses Denied", 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)))))), "")

Any help would be greatly appreciated!!!

Leibel S

@Renita Luck

Your formula above seems to be missing a parentheses, see bolded below:

IFERROR(IF([PC or Chair Approval]@row="All Courses Denied", "ALL COURSES DENIED", IF([PC or Chair]@row="", [All Courses Requested]@row, IF(AND([PC or Chair]@row<>"", [PC or Chair Approval]@row="All Courses Approved"), [All Courses Requested]@row, IF([PC or Chair]@row="Some Courses Denied", 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)))))), "")

Is there something else that is an issue?

Renita Luck

I was so excited that the missing parenthesis would fix it... unfortunately, I'm still getting the #UNPARSEABLE error message - any other ideas?

Leibel S

Are all the references named properly?

Are there any errors in the referenced cells?

Renita Luck

I selected the cells to ensure the references were named properly in the formula and the referenced cells are drop down selects where I copy/pasted the choices to avoid errors in spelling; so should not be any errors there.

The "All Courses Requested" column uses a join formula - but it is working perfectly and it is just a simple join.

I'm just honestly stumped. I've been looking at this and redoing the formula from scratch for several days now trying to figure out what I have missed.

BullandKhmer

HAS([All Courses Requested]@row, @cell)

HAS(@cell, [All Courses Requested]@row)

?

Leibel S

@Renita Luck

Try copying and pasting the formula as written above

Renita Luck

So, I'm back and hoping for more help. I'm using the formula below with the same scenario above - and it works in all aspects - except it will not remove the Courses Declined PC or Chair information using the Not/HAS the way it is in the formula - any suggestions?

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