Showcasing data from another sheet with negating criteria

Ali Simpson

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?

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()

=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"), NOT({Ref Sheet: Status} = "Onboard"), 1), "TBA")))

it is still showing some Onboard however,