Showcasing data from another sheet with negating criteria

I have built a formula that works! (Kinda) I just want to make it better

I am utilizing a reference sheet to see who is Incoming for a staffing situation. I ONLY want to see references to status labeled; "Confirmed", "Accepted", "Offered", "Pending", and "TBA" in that order (Confirmed as highest priority). Some references read "Cancelled", "Rejected", "Onboard" and I do not want these to be seen but some are sneaking through.

Columns "Inc. Crew Lvl" thru "Status coming" are all pulled utilizing formulas from another sheet

Currently the first reference point I am making is the "Inc. Crew Name" column using this formula

=IFERROR(INDEX(COLLECT({Ref Sheet: TMS Range 3}, {Ref Sheet: TMS Range 1}, MID@row, {Ref Sheet: TMS Range 4}, "Confirmed"), 1), IFERROR(INDEX(COLLECT({Ref Sheet: TMS Range 3}, {Ref Sheet: TMS Range 1}, MID@row, {Ref Sheet: TMS Range 4}, "Offered"), 1), IFERROR(INDEX(COLLECT({Ref Sheet: TMS Range 3}, {Ref Sheet: TMS Range 1}, MID@row, {Ref Sheet: TMS Range 4}, "Pending"), 1), "TBA")))

I then use the "Inc. Crew Name" as a reference point for the rest of the columns, with this it is pulling up reference points for "Status coming" being "Onboard" which I do not want.

How can I rectify this? Is there a way to put and Except into a formula?

Find more posts tagged with

Accepted answers

All comments

Adam Murphy

Yes, you can use NOT() to negate function/formula. Hope that helps!

Ali Simpson

Where in the formula would you put the NOT()

Original

=IFERROR(INDEX(COLLECT({Ref Sheet: Name}, {Ref Sheet: MID}, MID@row, {Ref Sheet: STATUS}, "Confirmed"), 1), IFERROR(INDEX(COLLECT({Ref Sheet: Name}, {Ref Sheet: MID}, MID@row, {Ref Sheet: STATUS}, "Offered"), 1), IFERROR(INDEX(COLLECT({Ref Sheet: Name}, {Ref Sheet: MID}, MID@row, {Ref Sheet: STATUS}, "Pending"), 1), "TBA")))

Updated with NOT()

it is still showing some Onboard however,

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.