Pull Cells Only From Another Sheet With Criteria

Joe Phelps

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!

jessica.smith

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

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

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

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

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)),"")

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

Can you show me your formula? Copy and paste?

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

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.

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

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.

Joe Phelps

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

jessica.smith

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

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.

Joe Phelps

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