Flags using IF AND formula with multiple sheets

Options

Hello,

I would like to create a formula where it flags for the following information:

Donor Center = other sheet column Donor Center AND Job Title = Ops Admin QAS AND other sheet column Position = Quality Assurance Supervisor

Then Donor center = other sheet column Donor Center AND Job Title = Ops Admin Assistant Mgr AND other sheet column Position = Assistant Manager

This is what I have so far but this one is giving me #INVALID OPERATION

=IF(AND([Donor Center]:[Donor Center] = @cell, {Open Positions Range 1} = @cell), 1, 0)

Any help would be greatly appreciated!

Tags:

• ✭✭✭✭✭✭
Options

Why not try this type of INDEX(MATCH()) formula?

=IFERROR(IF(OR(

AND(INDEX({Job Title}, MATCH([Donor Center]@row, {Donor Center}, 0)) = "Ops Admin QAS", INDEX({Position}, MATCH([Donor Center]@row, {Donor Center}, 0)) = "Quality Assurance Supervisor"),

AND(INDEX({Job Title}, MATCH([Donor Center]@row, {Donor Center}, 0)) = "Ops Admin Assistant Mgr", INDEX({Position}, MATCH([Donor Center]@row, {Donor Center}, 0)) = "Assistant Manager")

), 1, 0), 0)

I made a demo sheet imagining what you are trying to do and verified the formula, so have a look.

I created two other sheets.

With two sheets, first, the AND would be an AND (Job Title, Position) configuration, and the whole thing would be an OR (Sheet 1 AND, Shee1 AND, Sheet 2 AND, Sheet 2AND) configuration, but it didn't work, so I checked Sheet 1 and Sheet 2, and then OR (Sheet1, Sheet2) for the two sheets.

If this seems too complicated, you may want to use a report. Create a column of checkboxes on each sheet, such as Meet Condition, and include the following formula.

=IF(OR(AND([Job Title]@row = "Ops Admin QAS", Position@row = "Quality Assurance Supervisor"), [Job Title]@row = "Ops Admin Assistant Mgr", Position@ row = "Assistant Manager"), 1, 0)

Then, in the report, I think we should be able to filter to show those that have the Meet Condition checked.