Flags using IF AND formula with multiple sheets

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!

Find more posts tagged with

Formulas

Accepted answers

All comments

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.

Flags using IF AND formula with multiple sheets Demo DB.png

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.

Use report filtering with the Meet Condition Column.png

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

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.