Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Pull Cells Only From Another Sheet With Criteria

✭✭✭
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!

Best Answer

  • ✭✭✭✭✭✭
    Answer ✓

    To follow-up on resolution. The lingering issue was that the Auto Number column on the source sheet was returning a string, while the Req# column on the target sheet was being entered as a number. We modified the formula to an Index/Collect with a HAS function to resolve.

    Jessica Selano | Selano Consulting

    jessica@selanoconsulting.com

Answers

  • ✭✭✭✭✭✭

    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

  • ✭✭✭

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

  • ✭✭✭✭✭✭

    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

  • ✭✭✭

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

  • 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

  • ✭✭✭

    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!

  • 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

  • ✭✭✭

    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

  • 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

  • ✭✭✭

    @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

  • Community Champion
    edited 02/07/25

    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

  • ✭✭✭

    Thank you!! Ok so I put that in but it still isn't pulling the PO # into the cell?

  • ✭✭✭✭✭✭

    @Joe Phelps I think we can probably resolve this in less then 10 minutes if we do a live screen share. Feel free to send me an email to set up a time to connect if you are interested.

    Jessica Selano | Selano Consulting

    jessica@selanoconsulting.com

  • ✭✭✭✭✭✭
    Answer ✓

    To follow-up on resolution. The lingering issue was that the Auto Number column on the source sheet was returning a string, while the Req# column on the target sheet was being entered as a number. We modified the formula to an Index/Collect with a HAS function to resolve.

    Jessica Selano | Selano Consulting

    jessica@selanoconsulting.com

  • ✭✭✭

    @jessica.smith Thank you so much again! I really appreciate you taking the time to help me get this resolved!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions