Pull Cells Only From Another Sheet With Criteria

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
-
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?
-
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!
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!