Help with IF AND formula with cross sheet reference

Options

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.


Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/07/24 Answer ✓
    Options

    @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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @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.


    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • kelceyg
    kelceyg ✭✭✭✭✭
    Options

    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
    Mark.poole ✭✭✭✭✭✭
    Options

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/07/24 Answer ✓
    Options

    @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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • kelceyg
    kelceyg ✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!