To Pull a recent status from another sheet

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.

Paul Newcome

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

Happy to help. 👍️

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

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.

Paul Newcome

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

Hi @Paul Newcome,

Thank you, It works for me.

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

Hi

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

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

Hi @Paul Newcome

Thank you I will try this solution.

Have a fantastic Day !!

Paul Newcome

Happy to help. 👍️