HELP What am I doing wrong in this formula?

=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

Find more posts tagged with

Contacts

Formulas

Accepted answers

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

All comments

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

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!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.