Pull Cells Only From Another Sheet With Criteria

Joe Phelps
Joe Phelps ✭✭✭
edited 01/31/25 in Formulas and Functions

Good afternoon all,

I am trying to figure out how to have certain cells pulled over to a row on a new sheet when certain criteria are met.

I want the purchase order number in the purchase order column to pull onto another sheet only if the REQ # matches.

The purchase order number shows up in the purchase order column that is pulled from another sheet:

I want that purchase order number to pull into this sheet:

In the PO# cell only if whatever REQ # in the cell to the left matches it.

Thank you!

Answers

  • jessica.smith
    jessica.smith ✭✭✭✭✭

    Sounds like you want to use an Index/Match formula.

    Create two cross sheet references on the second sheet. One to the Req # column {PO_Req} and one to the PO column {PO_PO}.

    Then in the PO # column, use an index/match formula to look up the PO that matches the Req number in the Req # column: =IFERROR(INDEX({PO_PO}, MATCH([Req #]@row, {PO_Req}, 0)), "")

    Sheet to reference:

    Sheet with the lookup formula:

    Does that work for you?

    Jessica Selano | Selano Consulting

    jessica@selanoconsulting.com

  • Joe Phelps
    Joe Phelps ✭✭✭

    @jessica.smith Thank you! Is there a way to link them without having to put in the Match/Index formula on the REQ form? There is already a formula in there that is pulling from another sheet which I can't replace….

    Thank you!!

  • jessica.smith
    jessica.smith ✭✭✭✭✭

    Not sure if I'm misunderstanding the follow-up question, but the formula would be in the PO # column, not the REQ #.

    Jessica Selano | Selano Consulting

    jessica@selanoconsulting.com

  • Joe Phelps
    Joe Phelps ✭✭✭

    So the PO# column has the formula and then where can I pull the PO data from without re-writing the existing formula?

  • Michelle Choate 2
    Michelle Choate 2 Community Champion

    Hello @Joe Phelps - I believe what @jessica.smith was trying to say is that this formula would be in your new sheet.

    So the formula in the new sheet (bottom photo above) would be = IFERROR(INDEX({Purchase Order Number from top sheet above}, MATCH([REQ #]@row, {REQ # from top sheet above},0)),"")

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

  • Joe Phelps
    Joe Phelps ✭✭✭

    Hi @Michelle Choate 2 Thank you for the response! I put in that formula and linked it to each cell on the req form sheet but it's saying Syntax error? Sorry for the headache I'm relatively new to smartsheet!

  • Michelle Choate 2
    Michelle Choate 2 Community Champion

    Can you show me your formula? Copy and paste?

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

  • Joe Phelps
    Joe Phelps ✭✭✭

    Sure I put in

    =IFERROR(INDEX({PO_PO}, MATCH([REQ #]@row, {PO_Req}, 0)), "")

    And referenced my master req form sheet for the PO column and the REQ # column

  • Michelle Choate 2
    Michelle Choate 2 Community Champion

    So what that usually means is that one of your references has accidentally defaulted to a single cell, rather than the entire column. Can you hit "Edit Reference" and confirm that they are for the entire column, rather than just the top cell? That should be able to fix the Syntax issue.

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

  • Joe Phelps
    Joe Phelps ✭✭✭

    @Michelle Choate 2 Ok I re-did it with the PO # column from my Req form sheet and the REQ # column from my req form sheet but it says UNSPARSEABLE? Apologies for the back and forth just need it to reference! Haha

  • Michelle Choate 2
    Michelle Choate 2 Community Champion
    edited 2:13AM

    No worries at all @Joe Phelps !! Let's get this right! You cannot have {{ }}. It must be only { }.

    So in this case: =IFERROR(INDEX({REQ Form Range 2}, MATCH([REQ #]@row, {REQ Form Range 3}, 0)),"")

    Also - I ALWAYS recommend naming your ranges so that way when you need to trouble shoot a formula in the future, you know exactly what you were trying to reference in the beginning of creating the formula.

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!