To Pull a recent status from another sheet

Kaveri Vipat
Kaveri Vipat ✭✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    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})))

    PMP Certified

    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"

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    Hi Bassam.M Khalil 

    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⬆️"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    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⬆️"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    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⬆️"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    Hi @Paul Newcome

    Thank you I will try this solution.

    Have a fantastic Day !!

    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⬆️"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!