Help with IF AND formula with cross sheet reference

I am trying to write a cross sheet formula, and I keep getting an #INVALID OPERATION error, and I cannot puzzle it out.

My current formula: =IF(AND({Scenario REQ ID} = [REQ ID]@row, {Scenario Status} = "Pass"), 1, 0)

If the REQ ID from my scenario sheet matches the REQ ID of this row, AND the Status on the scenario sheet is "Pass", check the box.

Find more posts tagged with

Sheets

Formulas

functionality

Project Management

Accepted answers

Mark.poole

@kelceyg

After looking into it a bit it appears IF statements and cross sheet references do funny things. So I thought what if we did a Index Match instead.

=IF(Index({Scenario Status},Match([REQ ID]@row,{Scenario REQ ID},0))="Pass",1,0)

See how this works out for you.

All comments

Mark.poole

@kelceyg

Looking at your formula I can’t see an issue in and of itself. How ever since you changed the ref names from the defaults. Check to be sure the columns are still actually being referenced. I have ran into this issue before when selecting the column before changing the name.

kelceyg

I have double checked my column references, and they appear to be fine.

Just to be sure, I ended up deleting the formula, deleting the references out of the Sheet Reference Manager, saved the sheet, and then recreated the formula without changing the default reference names. I still got the same error message.

Mark.poole

is there any negative space in front of or behind the data in the reference rows?

Mark.poole

@kelceyg

After looking into it a bit it appears IF statements and cross sheet references do funny things. So I thought what if we did a Index Match instead.

=IF(Index({Scenario Status},Match([REQ ID]@row,{Scenario REQ ID},0))="Pass",1,0)

See how this works out for you.

kelceyg

@Mark.poole the Index Match formula worked! Thank you!

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.