Hello! Im attempting to use Index Match to return data from all my worksheets (there will be 50-75 in the end) based on selection from a drop down list. I have it working for one sheet just not all of them. I was hoping to maybe use a SheetList although I cannot figure out how to do this. Any help would be greatly appreciated!
P Helper(1480).xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | Lemon | SheetList | |||||||||||||||
2 | 355 | ||||||||||||||||
3 | 454 | ||||||||||||||||
4 | F | Date Used | AI | C2 | 407 | ||||||||||||
5 | 355 | Oct-30-20 | Okay | Free | 405 | ||||||||||||
6 | 355 | Feb-22-21 | Okay | Free | |||||||||||||
7 | 355 | Jan-22-20 | Okay | Free | |||||||||||||
8 | 355 | Mar-12-20 | Okay | Free | |||||||||||||
9 | 355 | June-30-20 | Okay | Free | |||||||||||||
10 | |||||||||||||||||
11 | |||||||||||||||||
12 | |||||||||||||||||
13 | |||||||||||||||||
14 | |||||||||||||||||
15 | |||||||||||||||||
16 | |||||||||||||||||
17 | |||||||||||||||||
18 | |||||||||||||||||
19 | |||||||||||||||||
20 | |||||||||||||||||
21 | |||||||||||||||||
22 | |||||||||||||||||
23 | |||||||||||||||||
24 | |||||||||||||||||
25 | |||||||||||||||||
26 | |||||||||||||||||
27 | |||||||||||||||||
28 | |||||||||||||||||
29 | |||||||||||||||||
30 | |||||||||||||||||
Desired Result |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B5:B27 | B5 | =IFERROR(INDEX('355'!C:C,AGGREGATE(15,6,ROW('355'!C$3:C$121)/('355'!B$3:B$121=A$1),ROWS(B$5:B5))),"") |
C5:C27 | C5 | =IFERROR(INDEX('355'!L:L,AGGREGATE(15,6,ROW('355'!L$3:L$121)/('355'!B$3:B$121=A$1),ROWS(C$5:C5))),"") |
D5:D27 | D5 | =IFERROR(INDEX('355'!K:K,AGGREGATE(15,6,ROW('355'!K$3:K$121)/('355'!B$3:B$121=A$1),ROWS(D$5:D5))),"") |
A5 | A5 | =IFERROR(INDEX('355'!A:A,AGGREGATE(15,6,ROW('355'!A$3:A$121)/('355'!B$3:B$121=A$1),ROWS(A$5:A5))),"") |
A6:A27 | A6 | =IFERROR(INDEX('355'!A:A,AGGREGATE(15,6,ROW('355'!A$3:A$121)/('355'!B$3:B$121=A$1),ROWS(A$5:A6))),"") |
A28:A30 | A28 | =IFERROR(INDEX('355'!A:A,AGGREGATE(15,6,ROW('355'!A$3:A$121)/('355'!B$3:B$121=A$1),ROWS(#REF!))),"") |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
A1:B2 | List | =Data!$B$2:$B$13 |
P Helper(1480).xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Number: 355 | Name: | ||||||||||||
2 | F | P | Date | MC | MU | MJ | MC2 | MK | MA | ME | C2 | AI | ||
3 | 355 | Grape | Jan-30-21 | 0.1 | 0.8 | 1.1 | 0.12 | 4.5 | 0 | 0 | Free | Good | ||
4 | 355 | Grape | Jan-30-21 | 0.1 | 0.8 | 1.1 | 0.12 | 4.5 | 0 | 0 | Free | Good | ||
5 | 355 | Mango | Jan-30-21 | 0.9 | 2.1 | 6.5 | 9.1 | 8.7 | 5.5 | 0.3 | Free | Poor | ||
6 | 355 | Lemon | Oct-30-20 | 0.55 | 2.9 | 9.9 | 5.6 | 1.9 | 8.9 | 0.3 | Free | Okay | ||
7 | 355 | Orange | Jan-19-21 | 0.2 | 8.2 | 4.2 | 0.1 | 2.3 | 5.5 | 2.2 | Final | Fine | ||
8 | 355 | Grape | Feb-22-21 | 0.1 | 0.8 | 1.1 | 0.12 | 4.5 | 0 | 0 | Free | Good | ||
9 | 355 | Lemon | Feb-22-21 | 0.55 | 2.9 | 9.9 | 5.6 | 1.9 | 8.9 | 0.3 | Free | Okay | ||
10 | 355 | Lime | Mar-20-21 | 0.23 | 9.1 | 9.1 | 7.14 | 5.1 | 5.1 | 8.9 | Final | Poor | ||
11 | 355 | Apple | June-20-21 | 0.01 | 0.2 | 0.1 | 0.1 | 0.05 | 0.05 | 0.1 | Final | Great | ||
12 | 355 | Lemon | Jan-22-20 | 0.55 | 2.9 | 9.9 | 5.6 | 1.9 | 8.9 | 0.3 | Free | Okay | ||
13 | 355 | Lemon | Mar-12-20 | 0.55 | 2.9 | 9.9 | 5.6 | 1.9 | 8.9 | 0.3 | Free | Okay | ||
14 | 355 | Lemon | June-30-20 | 0.55 | 2.9 | 9.9 | 5.6 | 1.9 | 8.9 | 0.3 | Free | Okay | ||
15 | 355 | |||||||||||||
355 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D3:D15 | D3 | =IFERROR(VLOOKUP(B3,Data!B$2:M$13,4,FALSE),"") |
E3:E15 | E3 | =IFERROR(VLOOKUP(B3,Data!B$2:M$13,5,FALSE),"") |
F3:F15 | F3 | =IFERROR(VLOOKUP(B3,Data!B$2:M$13,6,FALSE),"") |
G3:G15 | G3 | =IFERROR(VLOOKUP(B3,Data!B$2:M$13,7,FALSE),"") |
H3:H15 | H3 | =IFERROR(VLOOKUP(B3,Data!B$2:M$13,8,FALSE),"") |
I3:I15 | I3 | =IFERROR(VLOOKUP(B3,Data!B$2:M$13,9,FALSE),"") |
J3:J15 | J3 | =IFERROR(VLOOKUP(B3,Data!B$2:M$13,10,FALSE),"") |
K3:K15 | K3 | =IFERROR(VLOOKUP(B3,Data!B$2:M$13,2,FALSE),"") |
L3:L15 | L3 | =IFERROR(VLOOKUP(B3,Data!B$2:M$13,3,FALSE),"") |
C15 | C15 | =IFERROR(VLOOKUP(B15,Data!B$2:M$13,2,FALSE),"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
H4:J167 | Cell Value | between 0 and 0.05 | text | NO |
G4:G167 | Cell Value | between 0 and 0.05 | text | NO |
F4:F167 | Cell Value | between 0 and 0.05 | text | NO |
E4:E167 | Cell Value | between 0 and 0.05 | text | NO |
D4:D167 | Cell Value | between 0 and 0.05 | text | NO |
B4:B167 | Cell Value | ending with "*" | text | NO |
B4:B167 | Cell Value | ending with "'" | text | NO |
B4:B167 | Cell Value | ending with "," | text | NO |
B4:B167 | Cell Value | ending with "." | text | NO |
D3:J3 | Cell Value | between 0 and 0.05 | text | NO |
B3 | Cell Value | ending with "*" | text | NO |
B3 | Cell Value | ending with "'" | text | NO |
B3 | Cell Value | ending with "," | text | NO |
B3 | Cell Value | ending with "." | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B3:B15 | List | =Data!$B$2:$B$20 |