retrieve current status

Question

Hi

I have a pivot table which contain products going through a process (data tab). I wish to create a summaried table (batch summary tab) to show the final status showing 'open' or 'closed' (in column F) which can be retrieved from 'data tab' (in column O) . which formula show I used for this?

Answer 1

TechNet Forum is a better place for this issue; post this on TechNet: http://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

Answer 2

I can't post as I have account verification problem? how to solve that?

Answer 3

I understood clearly because I've hard work on it and finally found a solution for your requirement. I would like to thanks to you for sharing a nice request. I'm sure millions of Excel users will get help from your this topic in future.

Well, lets see how to use the formula in F2 cell of your "batch summary" sheet.

Step 1: Assume that, below is your data table of "data" sheet:

Step 2: And here is another data table of "batch summary" sheet:

Step 3: Use the below formula in "F2" cell of your "batch summary" sheet and press CTRL+SHIFT+ENTER due to it is an array formula:

=INDEX(data!$O:$O,MATCH($C3&MAX(IF($C3=data!$C:$C,data!$I:$I)),data!$C:$C&data!$I:$I,0))

To use the above formula properly, first "Copy" the above formula. Then in "batch summary" sheet, Click on F2 cell to select it. Now "Click" on formula bar to activate the cursor there and paste the formula. After paste it, press CTRL+SHIFT+ENTER. Now you will see the "Final Status". The formula will like this after pressing CTRL+SHIFT+ENTER:

{=INDEX(data!$O:$O,MATCH($C3&MAX(IF($C3=data!$C:$C,data!$I:$I)),data!$C:$C&data!$I:$I,0))}

Step 4: Now Copy the F2 cell of your "batch summary" sheet, where you have just seen the above formula with '{' '}', and copy down to the end of F5 as you have asked in your request. Now you will see the final status like below:

* Note that, I have used full column range like $O:$O, $C:$C, $I:$I, so that you don't need to change the column range each time you add new data on it. But You can set a custom column range if you wish. Below formula is only for your provided sample data range (Row 2 to 19 as range from "data" sheet):

=INDEX(data!$O$2:$O$19,MATCH($C3&MAX(IF($C3=data!$C$2:$C$19,data!$I$2:$I$19)),data!$C$2:$C$19&data!$I$2:$I$19,0))

Enjoy you time.

Answer 4

really thanks for your help. u really solve my problem.

Answer 5

really thanks for your help. u really solve my problem.

I found that the formula somehow is not updating the status and it is happening quite frequently. You can see below the batch summary is not updated though the data tab information I change it to close.