I have the below grid table of data with X and Y axis information. I would like to only pull the numbers where the X and Y names are intersecting and from a subset of information. So in one sheet (image 1) i have the below information. On another sheet (image 2) i have the names of the players that i want to pull. The reason for this second sheet is it is hard for me to scan a grid table to find the lowest score with the players eligible (image 2). I have a conditional format (from another thread) that colors those in sheet 1 that are playing (sheet 2). However i would like sheet two to have the third column populate the net score so then in column c (sheet 2, third column) i can do a quick sort
SHEET/IMAGE 1. This tab is called "Scores"
SHEET/IMAGE 2 . The third column is where i want to populate the net score pulled from sheet/image 1. This tab is called "Names"
SHEET/IMAGE 1. This tab is called "Scores"
Ham and Egg Spreadsheet.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | |||
1 | Alston Jason | Archuleta Michael | Baker Bea | Bledsoe Darrell | Bolden Anthony | Booker Richard | Brister Antonio | Brown Kendall | Colebrook James | Collebrook Sean | Dubose Damitoin | Edmonds Jojo | Edmonds Scott | Evans Booker | |||
2 | Alston Jason | 67 | 55 | 77 | 72 | 64 | 68 | 72 | 69 | 72 | 70 | 62 | 56 | 59 | |||
3 | Archuleta Michael | 67 | 55 | 72 | 67 | 62 | 68 | 71 | 68 | 67 | 69 | 60 | 54 | 59 | |||
4 | Baker Bea | 55 | 55 | 55 | 55 | 51 | 54 | 55 | 55 | 54 | 55 | 50 | 47 | 49 | |||
5 | Bledsoe Darrell | 77 | 72 | 55 | 76 | 67 | 76 | 82 | 74 | 76 | 74 | 64 | 56 | 61 | |||
6 | Bolden Anthony | 72 | 67 | 55 | 76 | 63 | 69 | 74 | 69 | 70 | 68 | 61 | 55 | 59 | |||
7 | Booker Richard | 64 | 62 | 51 | 67 | 63 | 62 | 66 | 62 | 63 | 64 | 57 | 52 | 55 | |||
8 | Brister Antonio | 68 | 68 | 54 | 76 | 69 | 62 | 74 | 70 | 70 | 68 | 60 | 54 | 60 | |||
9 | Brown Kendall | 72 | 71 | 55 | 82 | 74 | 66 | 74 | 74 | 74 | 72 | 62 | 55 | 59 | |||
10 | Colebrook James | 69 | 68 | 55 | 74 | 69 | 62 | 70 | 74 | 70 | 68 | 59 | 54 | 60 | |||
11 | Collebrook Sean | 72 | 67 | 54 | 76 | 70 | 63 | 70 | 74 | 70 | 70 | 62 | 56 | 60 | |||
12 | Dubose Damitoin | 70 | 69 | 55 | 74 | 68 | 64 | 68 | 72 | 68 | 70 | 58 | 54 | 60 | |||
13 | Edmonds Jojo | 62 | 60 | 50 | 64 | 61 | 57 | 60 | 62 | 59 | 62 | 58 | 53 | 54 | |||
14 | Edmonds Scott | 56 | 54 | 47 | 56 | 55 | 52 | 54 | 55 | 54 | 56 | 54 | 53 | 50 | |||
15 | Evans Booker | 59 | 59 | 49 | 61 | 59 | 55 | 60 | 59 | 60 | 60 | 60 | 54 | 50 | |||
Scores |
Cell Formulas | ||
---|---|---|
Range | Formula | |
X2:AK15 | X2 | =IF($W2=X$1,"",LET(tbl,$B$5:$S$10983,n,$A$5:$A$10983,r,INDEX(tbl,MATCH($W2,n,0)+1,0),c,INDEX(tbl,MATCH(X$1,n,0)+1,0),SUM(IF(r<c,r,c)))) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
X103:EJ213,X81:X102,X2:EJ80 | Expression | =COUNTIFS(Names!$A$2:$A$500,$W2,Names!$B$2:$B$500,X$1) | text | NO |
SHEET/IMAGE 2 . The third column is where i want to populate the net score pulled from sheet/image 1. This tab is called "Names"
Ham and Egg Spreadsheet.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Partner A | Partner B | NET SCORE | ||
2 | Alston Jason | Alston Jason | |||
3 | Alston Jason | Archuleta Michael | |||
4 | Alston Jason | Baker Bea | |||
5 | Alston Jason | Bledsoe Darrell | |||
6 | Alston Jason | Bolden Anthony | |||
7 | Alston Jason | Booker Richard | |||
8 | Alston Jason | Brister Antonio | |||
9 | Alston Jason | Brown Kendall | |||
10 | Alston Jason | Colebrook James | |||
11 | Alston Jason | Collebrook Sean | |||
12 | Alston Jason | Dubose Damitoin | |||
13 | Alston Jason | Edmonds Jojo | |||
14 | Alston Jason | Edmonds Scott | |||
15 | Alston Jason | Evans Booker | |||
16 | Evans Booker | Edmonds Jojo | |||
17 | Evans Booker | Baker Bea | |||
18 | Bolden Anthony | Baker Bea | |||
19 | Brown Kendall | Archuleta Michael | |||
Names |