Hi
Thank you for taking the time to read this post, I just want to know how to reveal the names of the countif formulas for my criteria using vba. I've attached 2 sheets the second sheet is my 2 way table. I just want to be able to click on a cell eg i8 and reveal the three names that match my criteria. Many thanks for any help.
Stay safe.
Thank you for taking the time to read this post, I just want to know how to reveal the names of the countif formulas for my criteria using vba. I've attached 2 sheets the second sheet is my 2 way table. I just want to be able to click on a cell eg i8 and reveal the three names that match my criteria. Many thanks for any help.
Stay safe.
countif_names.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | forename | surname | expected result | actual test result | ||
2 | Jonathan | Edwards | E | D | ||
3 | Richard | Carter | B | C | ||
4 | Paul | Phillips | C | D | ||
5 | Theresa | Ross | A | B | ||
6 | Nicole | Bennett | C | B | ||
7 | Shawn | Kelly | C | B | ||
8 | Tammy | Foster | E | C | ||
9 | Jeremy | Gonzales | F | D | ||
10 | Earl | Martinez | B | A | ||
11 | Raymond | Jenkins | B | B | ||
12 | Bobby | Simmons | A | B | ||
13 | Debra | Long | D | C | ||
14 | Justin | Robinson | D | C | ||
15 | Joyce | Baker | E | F | ||
16 | Larry | Jones | C | C | ||
17 | Ryan | Campbell | B | B | ||
18 | Frances | Miller | A | A* | ||
19 | Philip | Butler | A* | A | ||
20 | Christina | Sanchez | A | A | ||
21 | Carlos | Bryant | A* | A | ||
22 | Gary | Cox | C | B | ||
23 | Robert | Wright | B | C | ||
24 | Craig | Lewis | B | C | ||
25 | James | Hernandez | D | C | ||
26 | Irene | Moore | A* | B | ||
Data |
countif_names.xlsx | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | |||||||||||||||||||
2 | Actual Grades | ||||||||||||||||||
3 | U | G | F | E | D | C | B | A | A* | ||||||||||
4 | Estimated grades | U | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Below grade | |||||||
5 | G | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||
6 | F | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | on grade | ||||||||
7 | E | 0 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | |||||||||
8 | D | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | Above grade | ||||||||
9 | C | 0 | 0 | 0 | 0 | 1 | 1 | 3 | 0 | 0 | |||||||||
10 | B | 0 | 0 | 0 | 0 | 0 | 3 | 2 | 1 | 0 | |||||||||
11 | A | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 1 | 1 | |||||||||
12 | A* | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 0 | |||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D4 | D4 | =COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"U") |
E4 | E4 | =COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"G") |
F4 | F4 | =COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"F") |
G4 | G4 | =COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"E") |
H4 | H4 | =COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"D") |
I4 | I4 | =COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"C") |
J4 | J4 | =COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"B") |
K4 | K4 | =COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"A") |
L4 | L4 | =COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"A~*") |
D5 | D5 | =COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"U") |
E5 | E5 | =COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"G") |
F5 | F5 | =COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"F") |
G5 | G5 | =COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"E") |
H5 | H5 | =COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"D") |
I5 | I5 | =COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"C") |
J5 | J5 | =COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"B") |
K5 | K5 | =COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"A") |
L5 | L5 | =COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"A~*") |
D6 | D6 | =COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"U") |
E6 | E6 | =COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"G") |
F6 | F6 | =COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"F") |
G6 | G6 | =COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"E") |
H6 | H6 | =COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"D") |
I6 | I6 | =COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"C") |
J6 | J6 | =COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"B") |
K6 | K6 | =COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"A") |
L6 | L6 | =COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"A~*") |
D7 | D7 | =COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"U") |
E7 | E7 | =COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"G") |
F7 | F7 | =COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"F") |
G7 | G7 | =COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"E") |
H7 | H7 | =COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"D") |
I7 | I7 | =COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"C") |
J7 | J7 | =COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"B") |
K7 | K7 | =COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"A") |
L7 | L7 | =COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"A~*") |
D8 | D8 | =COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"U") |
E8 | E8 | =COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"G") |
F8 | F8 | =COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"F") |
G8 | G8 | =COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"E") |
H8 | H8 | =COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"D") |
I8 | I8 | =COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"C") |
J8 | J8 | =COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"B") |
K8 | K8 | =COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"A") |
L8 | L8 | =COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"A*") |
D9 | D9 | =COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"U") |
E9 | E9 | =COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"G") |
F9 | F9 | =COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"F") |
G9 | G9 | =COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"E") |
H9 | H9 | =COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"D") |
I9 | I9 | =COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"C") |
J9 | J9 | =COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"B") |
K9 | K9 | =COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"A") |
L9 | L9 | =COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"A~*") |
D10 | D10 | =COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"U") |
E10 | E10 | =COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"G") |
F10 | F10 | =COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"F") |
G10 | G10 | =COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"E") |
H10 | H10 | =COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"D") |
I10 | I10 | =COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"C") |
J10 | J10 | =COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"B") |
K10 | K10 | =COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"A") |
L10 | L10 | =COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"A~*") |
D11 | D11 | =COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"U") |
E11 | E11 | =COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"G") |
F11 | F11 | =COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"F") |
G11 | G11 | =COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"E") |
H11 | H11 | =COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"D") |
I11 | I11 | =COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"C") |
J11 | J11 | =COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"B") |
K11 | K11 | =COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"A") |
L11 | L11 | =COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"A~*") |
D12 | D12 | =COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"U") |
E12 | E12 | =COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"G") |
F12 | F12 | =COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"F") |
G12 | G12 | =COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"E") |
H12 | H12 | =COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"D") |
I12 | I12 | =COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"C") |
J12 | J12 | =COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"B") |
K12 | K12 | =COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"A") |
L12 | L12 | =COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"A~*") |