To Pull a recent status from another sheet

04/21/21
Accepted

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.

Best Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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 NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Happy to help. 👍️

Answers

  • Bassam KhalilBassam 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})))

    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

  • Kaveri VipatKaveri 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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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 VipatKaveri Vipat ✭✭✭✭

    Hi @Paul Newcome,

    Thank you, It works for me.

  • Paul NewcomePaul 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 VipatKaveri Vipat ✭✭✭✭

    Hi

    Right now we have 6 status but it may increase after some time. Thanks for the Help.

  • Paul NewcomePaul 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 VipatKaveri Vipat ✭✭✭✭

    Hi @Paul Newcome

    Thank you I will try this solution.

    Have a fantastic Day !!

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Happy to help. 👍️

Sign In or Register to comment.