Flags using IF AND formula with multiple sheets

N.Derkiss

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!

jmyzk_cloudsmart_jp

Hi @N.Derkiss

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.

https://app.smartsheet.com/b/publish?EQBCT=3798bb6d1b44427788b0eff50ca5397b

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.

https://app.smartsheet.com/b/publish?EQBCT=290c7c9ab0ef4c2fa9b6f09b9c899419