Help with IF AND formula with cross sheet reference

kelceyg

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.

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.

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!