What formula do I use?
I have 2 sheets. Sheet one is a grading sheet that goes over appearance, aroma, feel ect. of a production lot. Sheet two is a stock list showing all production lots. Both sheets have the primary column set at Ample number (ID #'s). I want to use a formula in sheet two that copies the final grade from sheet one if the numbers in the primary column match.
So as you see in the pictures provided in the primary column there is a number "1220", I want to copy the "Final Grade of Product" cell from that row on sheet one and add it to the "Final Grade" cell in the row with the matching "1220" in sheet two. What formula would I use and how would I wright it?
Best Answers
-
Hello @Nate420
You can accomplish this by using an INDEX MATCH formula:
First in Sheet 2 under "Final Grade" type =INDEX(
Click Reference Another Sheet
Reference Sheet 1
Select the column you want to display as my screen shot shows below
Click Insert Reference.
Then use the MATCH to the cell of ample #,
Then Reference Sheet 1 again, for the Ample Number column:
Final Formula Resulting
=INDEX({Sheet 1 Range 1}, MATCH([Ample #]@row, {Sheet 1 Range 2}, 0))
Let me know if this works for you!
-
I believe this can be done. However, there are some requirements.
You need a Column that contains the Dates of the grade.
Looking at a similar scenario to yours in a previous post it appears INDEX(COLLECT may do the trick.
You can also use a MAX formula to pull the most recent dates.
https://community.smartsheet.com/discussion/80678/looking-for-most-recent-date
I hope this helps.
Answers
-
Hello @Nate420
You can accomplish this by using an INDEX MATCH formula:
First in Sheet 2 under "Final Grade" type =INDEX(
Click Reference Another Sheet
Reference Sheet 1
Select the column you want to display as my screen shot shows below
Click Insert Reference.
Then use the MATCH to the cell of ample #,
Then Reference Sheet 1 again, for the Ample Number column:
Final Formula Resulting
=INDEX({Sheet 1 Range 1}, MATCH([Ample #]@row, {Sheet 1 Range 2}, 0))
Let me know if this works for you!
-
This worked great thank you very much Mr. Chris
-
@Mr. Chris Is there a way that I could add more to this formula? I have just realized that we may have multiple grades for the same ID number as we test in every stage of production. Is there a way to only match the most recent grade with the date that matches that ID number?
-
I believe this can be done. However, there are some requirements.
You need a Column that contains the Dates of the grade.
Looking at a similar scenario to yours in a previous post it appears INDEX(COLLECT may do the trick.
You can also use a MAX formula to pull the most recent dates.
https://community.smartsheet.com/discussion/80678/looking-for-most-recent-date
I hope this helps.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!