Data Extraction from Other Sheets Based on the Value from the Dropdown List

Originally Posted by Bo_Ry

Please try at

A3
=SORT(CHOOSE(1+(B1="All"),FILTER(FILTER('Active Staff w Salary'!A2:F999,D1='Active Staff w Salary'!B2:B999),ISNUMBER(MATCH('Active Staff w Salary'!A1:F1,A2:F2,))),FILTER('ALL Staff List'!A2:A999,'ALL Staff List'!B2:B999=D1)))

C3
=MMULT(N(+A3#),SEQUENCE(COLUMNS(A3#),,,0))

Attachment 747441

Sorry. It doesn't work. When I changed the value in D1, it showed like that. Kindly look at the above pic.

Originally Posted by CARACALLA

RESULT


A3=IF(B1="ACTIVE",FILTER(FILTER('Active Staff w Salary'!$A$2:$F$35,'Active Staff w Salary'!B2:B35=RESULT!D1),{1,0,1,1,1,1}),FILTER('ALL Staff List'!A2:A121,'ALL Staff List'!B2:B121=RESULT!D1))

Attachment 747442

Sorry. It doesn't work. When I changed the value in D1, it showed like that. Kindly look at the above pic.

Originally Posted by wk9128

Worksheet name : RESULT
Cell A2 formula , Drag right

HTML Code: 

Cell A3 formula , Drag down and across

HTML Code: 

Hello! Unfortunately, in my real workbook, the sheet name doesn't include "All" and "Active". I tried to type the sheet name instead of "&$B$1&"*"&" in the formula. But it doesn't work. So can you kindly tell me where to put the sheet name in the correct way? Thanks a lot.

Pls tell me your real workbook, what is the name of the worksheet label name ? B1 must correspond to your worksheet

Please watch this animation teaching file FILE name as S17.gif
After the file is downloaded and decompressed, you can use the left mouse button to click twice to watch the teaching file (video) or open it with explorer.

Originally Posted by wk9128

Pls tell me your real workbook, what is the name of the worksheet label name ? B1 must correspond to your worksheet

Please watch this animation teaching file FILE name as S17.gif
After the file is downloaded and decompressed, you can use the left mouse button to click twice to watch the teaching file (video) or open it with explorer.

The worksheet names are "Staff Data" and "Details of Salary" in my real workbook.
Actually, the results that need to show have become much more complicated.
I've attached another workbook. Kindly check it and I'd be really grateful if you can help me figure out the right formula.
I wrote the results I wanted in the worksheet "Summary of Report".

Thank you so much!

Originally Posted by kvsrinivasamurthy

In A3 copied down

Formula:

=IFERROR(IF($B$1="Active",INDEX('Active Staff w Salary'!$A$1:$A$2000,AGGREGATE(15,6,ROW($A$2:$A$2000)/(('Active Staff w Salary'!$A$2:$A$2000<>"")*('Active Staff w Salary'!$B$2:$B$2000=$D$1)),ROWS($A$2:$A2))),INDEX('ALL Staff List'!$A$1:$A$2000,AGGREGATE(15,6,ROW($A$2:$A$2000)/(('ALL Staff List'!$A$2:$A$2000<>"")*('ALL Staff List'!$B$2:$B$2000=$D$1)),ROWS($A$2:$A2)))),"")

In B# copied for columns B to E

Formula:

=IFERROR(INDEX('Active Staff w Salary'!$C$1:$F$2000,AGGREGATE(15,6,ROW($A$2:$A$2000)/(('Active Staff w Salary'!$A$2:$A$2000=$A3)*('Active Staff w Salary'!$B$2:$B$2000=$D$1)),1),MATCH(B$2,'Active Staff w Salary'!$C$1:$F$1,0)),"") 

Thank you so much for your answer.
But the results that need to show have become much more complicated.
I've attached another workbook. Kindly check it and I'd be really grateful if you can help me figure out the right formula.
I wrote the results I wanted in the worksheet "Summary of Report".

Thank you again!

Originally Posted by kvsrinivasamurthy

Pl see the file with formulas.

Thank you so much for being this helpful.
It works perfectly.

Why in my file works ?