To Pull a recent status from another sheet
Hi
Hope you are doing well.
I have one sheet "Sheet 1", in which we have 2 fields that will be filled through the form. One for scanning the barcode of the Product and 1 for updating the status of the Product Process for ex- Raw material at the warehouse, Raw material in the manufacturing process, etc.
In the 2nd Sheet, I want to show the recent Status of the Product from sheet 1.
If in sheet 1, there are 6 status and each status is changing at different points as the process goes on.
So I want to show the recent status on sheet 2 and I am thinking to fetch the status through vlookup.
Please provide an effective solution so I implement it on my project.
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
Best Answers
-
I would suggest inserting a helper column in the source sheet to assign a number to the different statuses. Then you can use something along the lines of this to pull the highest number for that product...
=MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row))
Then we can nest that in a nested IF statement to say that if it is 1 then output the status of "A", 2 would output the status of "B", so on and so forth.
=IF(MAX(COLLECT(.....)) = 1, "A", IF(MAX(COLLECT(.....)) = 2, "B", IF(MAX(COLLECT(.....)) = 3, "C", IF(MAX(COLLECT(.....)) = 4, "D", IF(MAX(COLLECT(.....)) = 5, "E")))))
=IF(MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)) = 1, "A", IF(MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)) = 2, "B", IF(MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)) = 3, "C", IF(MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)) = 4, "D", IF(MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)) = 5, "E")))))
-
Happy to help. 👍️
Answers
-
Hi @Kaveri Vipat
Hope you are fine, you need to do the following.
1- in sheet 1 ( which you submit the status using the form ) add the system column ( modified date ) & the system column (Auto-Number).
2- in sheet 2 to collect recent status use the following formula.
recent status =JOIN(COLLECT({Status}, {Modified date}, MAX({Modified date})))
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thank you for the Help.
As per the new requirement, I am updating my Question.
In Sheet 1 the following Status is - A, B, C, D, E, and these should be in the sequence wise. But if in case at some point the person missed updating the status as per the flow and later He/She scan the barcode and update the Status so it looks like-
Product 1 D
Product 1 E
Product 1 C
Product 1 B
Product 1 A
As it completes all the stages according to this the recent status is D but I want to show the Status "E" according to the Workflow, in Sheet 2.
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
-
I would suggest inserting a helper column in the source sheet to assign a number to the different statuses. Then you can use something along the lines of this to pull the highest number for that product...
=MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row))
Then we can nest that in a nested IF statement to say that if it is 1 then output the status of "A", 2 would output the status of "B", so on and so forth.
=IF(MAX(COLLECT(.....)) = 1, "A", IF(MAX(COLLECT(.....)) = 2, "B", IF(MAX(COLLECT(.....)) = 3, "C", IF(MAX(COLLECT(.....)) = 4, "D", IF(MAX(COLLECT(.....)) = 5, "E")))))
=IF(MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)) = 1, "A", IF(MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)) = 2, "B", IF(MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)) = 3, "C", IF(MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)) = 4, "D", IF(MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)) = 5, "E")))))
-
Hi @Paul Newcome,
Thank you, It works for me.
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
-
Happy to help. 👍️
Depending on how many different status you have, we could also build out a quick table and reference that in place of the nested IF if that happens to get too long.
-
Hi
Right now we have 6 status but it may increase after some time. Thanks for the Help.
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
-
Ok. So build out the table, you would simply list each status in a single column in the order you would want them in. So continuing with the established examples, your table would look like this:
A
B
C
D
E
Then in place of the nested IF, we would use an INDEX function to reference this column and then use the MAX/COLLECT to determine which row to pull from the table.
=INDEX({Status Table Column}, MAX(COLLECT(.....)))
=INDEX({Status Table Column}, MAX(COLLECT({Source Sheet Number Column}, {Source Sheet Product Column}, [Product Column]@row)))
-
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!