HELP What am I doing wrong in this formula?

Casie

=INDEX(COLLECT({Unit/Manager List WI Range 1}, {Unit/Manager List WI Range 7}, [Area of Study Parent]@row, {Unit/Manager List WI Range 8}, [Area of Study - Nursing]@row, {Unit/Manager List WI Range 9}, [Area of Study - Radiography]@row, {Unit/Manager List WI Range 10}, [Area of Study - Rehab Services - PT/OT/ST]@row, {Unit/Manager List WI Range 11}, [Area of Study - Other]@row}), 1)

The above formula is resulting in #unparseable. Below I have Form 1 (UNIT/MANAGE List WI) and Form 2 (AW Placement Request)

My goal is:

When AW Request Status column is "Denied" on sheet 2 then collect "Unit Manager Email" from sheet 1 and place into column " Denied Assign To" on sheet 2.

When the Area of study-parent, Nursing, Radiography, Rehab, and other on sheet 1 matches Area of study parent, area of study - nursing, area of study - radiography, area of study - Rehab Services - PT/OT/ST, and/or Area of study - other on sheet 2

Kelly Moore

Hey @Casie

There is an extra curly bracket in your very last criteria  [Area of Study - Other]@row}

Remove that and the unparseable syntax error should be eliminated.

To only pull in the email address when the Status is denied, you will have to lead off the INDEX/COLLECT with an IF statement

=IF([AW Request Status]@row="Denied", =INDEX(COLLECT({Unit/Manager List WI Range 1}, {Unit/Manager List WI Range 7}, [Area of Study Parent]@row, {Unit/Manager List WI Range 8}, [Area of Study - Nursing]@row, {Unit/Manager List WI Range 9}, [Area of Study - Radiography]@row, {Unit/Manager List WI Range 10}, [Area of Study - Rehab Services - PT/OT/ST]@row, {Unit/Manager List WI Range 11}, [Area of Study - Other]@row), 1))

Will this work for you?

Kelly

Kelly Moore

Hey @Casie

There is an extra curly bracket in your very last criteria  [Area of Study - Other]@row}

Remove that and the unparseable syntax error should be eliminated.

To only pull in the email address when the Status is denied, you will have to lead off the INDEX/COLLECT with an IF statement

=IF([AW Request Status]@row="Denied", =INDEX(COLLECT({Unit/Manager List WI Range 1}, {Unit/Manager List WI Range 7}, [Area of Study Parent]@row, {Unit/Manager List WI Range 8}, [Area of Study - Nursing]@row, {Unit/Manager List WI Range 9}, [Area of Study - Radiography]@row, {Unit/Manager List WI Range 10}, [Area of Study - Rehab Services - PT/OT/ST]@row, {Unit/Manager List WI Range 11}, [Area of Study - Other]@row), 1))

Will this work for you?

Kelly

Casie

Yes! I ended up changing my cross sheet references and used this =IF([AW Request Status]@row = "Denied", INDEX(COLLECT({Coordinator Email}, {Area of Study - Parent}, [Area of Study Parent]@row, {Nursing}, [Area of Study - Nursing]@row, {Radiography}, [Area of Study - Radiography]@row, {Rehab}, [Area of Study - Rehab Services - PT/OT/ST]@row, {Other}, [Area of Study - Other]@row), 1), "")

AND IT WORKED PERFECTLY! THANK YOU!!