IF Formula

Setting up a formula where Tier I = 1500, Tier II = 1200, otherwise it's 1000. This formula setup works for me with in-sheet references, but not cross-sheet references... not sure how to adjust for the cross-sheet.

=IF({MASTER TRACKING SHEET Range 1}@cell = "Tier I", "1500", IF({MASTER TRACKING SHEET Range 1}@cell = "Tier II", "1200", "1000"))


Thank you!

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @aecross Not sure your problem description is complete. What is it in the sheet with the formula that matches to something in the cross-referenced sheet? You need to make that connection, don't you? You may need to build an index formula that connects the cross-ref range to the row of the sheet you are in.

    dm

  • aecross
    aecross ✭✭✭

    @Dale Murphy

    I've indexed this correctly and it pulls in the Tier level. My next question is how to incorporate the IF statement into an index formula? With the below i am getting an unparsable error.

    =IF(INDEX({MASTER TRACKING SHEET Range 6}, MATCH([JOB #]@row, {MASTER TRACKING SHEET JOB NUMBER}, 0), {MASTER TRACKING SHEET Range 1}@cell = "Tier I", "1500", {MASTER TRACKING SHEET Range 1}@cell = "Tier II", "1200", "1000"))

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @aecross You want your IF to do this: IF (<the INDEX statement, that returns some result> EQUALS <your condition of "Tier 1"> COMMA ...

    I can see that the INDEX statement doesn't close properly. I think you need a closing bracket here (at the end):

    IF(INDEX({MASTER TRACKING SHEET Range 6}, MATCH([JOB #]@row, {MASTER TRACKING SHEET JOB NUMBER}, 0))

    and then you need to evaluate what it returns against your condition.

    dm

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!