Highlighting Specific Cells In Another Table

[rackem12]

So I have a spreadsheet and I am trying to highlight the team name in the 2nd table, based on the next blank cell in the first table. What I am hoping for is that after i put in the score in table 1, it will change the highlight in table 2 to the next matchup. This will allow me to better see the standings move as I input each result.

Example:
Table1

Home	Away	H-Points	A-Points	H-Result	A-Result
Afghanistan	Albania	0.12	0.62	L	W
Zimbabwe	Andorra	0.74	0.07	W	L
Zambia	Angola	0.47	0.22	W	L
Yemen	Anguilla	0.98	0.00	W	L
Wales	Antigua & Barbuda

<colgroup><col style="mso-width-source:userset;mso-width-alt:5319; width:112pt" width="150" span="2"> <col style="mso-width-source:userset;mso-width-alt:2190; width:46pt" width="62" span="2"> <col style="mso-width-source:userset;mso-width-alt:2190; width:46pt" width="62" span="2"> </colgroup><tbody>
</tbody>

Table 2

Rank	Team	Conf.	GP	W	L	T	GB	Win %	PF	PA	PT	PD	Rtg	RTG Diff
1	Brazil	CONMEBOL	6	6	0	0		1.000	5.41	0.10	0.49	5.31	1.0000
2	Colombia	CONMEBOL	6	6	0	0		1.000	5.33	0.14	0.53	5.19	0.9999	0.0000
3	Austria	Europe	7	7	0	0	-0.5	1.000	6.16	0.23	0.61	5.93	0.9999	0.0001
4	Chile	CONMEBOL	6	6	0	0		1.000	5.05	0.23	0.72	4.82	0.9998	0.0001
5	Italy	Europe	6	6	0	0		1.000	4.99	0.25	0.76	4.74	0.9997	0.0001

<colgroup><col style="mso-width-source:userset;mso-width-alt:1365;width:29pt" width="38"> <col style="mso-width-source:userset;mso-width-alt:4864;width:103pt" width="137"> <col style="mso-width-source:userset;mso-width-alt:4949;width:104pt" width="139"> <col style="mso-width-source:userset;mso-width-alt:1336; width:28pt" width="38" span="2"> <col style="mso-width-source:userset;mso-width-alt:796; width:17pt" width="22" span="2"> <col style="mso-width-source:userset;mso-width-alt:1336;width:28pt" width="38"> <col style="mso-width-source:userset;mso-width-alt:1706;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:1166; width:25pt" width="33" span="2"> <col style="mso-width-source:userset;mso-width-alt:1934; width:41pt" width="54" span="2"> <col style="mso-width-source:userset;mso-width-alt:2360;width:50pt" width="66"> <col style="mso-width-source:userset;mso-width-alt:2190;width:46pt" width="62"> </colgroup><tbody>
</tbody>

As I stated, My goal is that Wales and Antigua & Barbuda will be highlighted in table 2, but once I input their scores, it will automatically highlight the next matchup in the standings table.

Thanks in advance.

 

[Yongle]

1. Are the tables tabulated values ot structured tables created with Insert Table?
2. Are the tables in different sheets?

 

[Yongle]

Assumes tabulated data in 2 sheets (Table 1 and Table 2)

One way is to add a formula to determine the row to be highlighted in Table 1 and use conditional formatting in the other sheet

1. In sheet "Table 1"

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Home	Away	H-Points	A-Points	H-Result	A-Result	Next Row	Formula in Column G
2​	Afghanistan	Albania	0.12​	0.62​	L	W		=IF(AND(COUNTBLANK(C1:F1)=0,COUNTBLANK(C2:F2)>0),"Next","")
3​	Zimbabwe	Andorra	0.74​	0.07​	W	L		=IF(AND(COUNTBLANK(C2:F2)=0,COUNTBLANK(C3:F3)>0),"Next","")
4​	Zambia	Angola	0.47​	0.22​	W	L		=IF(AND(COUNTBLANK(C3:F3)=0,COUNTBLANK(C4:F4)>0),"Next","")
5​	Yemen	Anguilla	0.98​	0​	W	L		=IF(AND(COUNTBLANK(C4:F4)=0,COUNTBLANK(C5:F5)>0),"Next","")
6​	Wales	Antigua & Barbuda	0.5​	0.7​	L	W		=IF(AND(COUNTBLANK(C5:F5)=0,COUNTBLANK(C6:F6)>0),"Next","")
7​	Albania	Angola					Next	=IF(AND(COUNTBLANK(C6:F6)=0,COUNTBLANK(C7:F7)>0),"Next","")
8​	Afghanistan	Albania						=IF(AND(COUNTBLANK(C7:F7)=0,COUNTBLANK(C8:F8)>0),"Next","")
9​

Sheet: Table 1

2. Select column B in sheet "Table 2" \ Conditional Formatting \ use formula \ this formula

=COUNTIFS('Table 1'!A:A,B1,'Table 1'!G:G,"Next")+COUNTIFS('Table 1'!B:B,B1,'Table 1'!G:G,"Next")

Excel 2016 (Windows) 32 bit

	A​	B​
1​	Rank	Team
2​	1​	Brazil
3​	2​	Colombia
4​	3​	Austria
5​	4​	Chile
6​	5​	Italy
7​	6​	Afghanistan
8​	7​	Zimbabwe
9​	8​	Zambia
10​	9​	Yemen
11​	10​	Wales
12​	11​	Albania
13​	12​	Andorra
14​	13​	Angola
15​	14​	Anguilla
16​	15​	Antigua & Barbuda

Sheet: Table 2

 

[rackem12]

Yongle,

Thanks for the reply. They next function worked like a charm but the conditional formatting seems to be stuck highlighting the wrong cell. For example, the next line is showing up for my Columbia vs Swaziland match up, but yet it is highlighting Ireland and Comoros for some reason.

they tables are on the same sheet, with a buffer in between.

 

[Yongle]

the tables are on the same sheet, with a buffer in between

This is what is causing the problem - Excel is finding things in the wrong table!

I need the row numbers..
Table 1 - Header row
Table 1 - Last row
Table 2 - Header row

Are you familiar with named ranges?

 

[rackem12]

Somewhat familiar with named ranges. May need a crash course.

I keep the results and standings on the same sheet in so that way I don't have to keep going back and forth to see the standings as they update.

 

[Yongle]

and the row numbers ???
Table 1 - Header row
Table 1 - Last row
Table 2 - Header row

 

[rackem12]

Sorry. Forgot to add.

The headers are all in row 1. the current last row for the entire table is 833 (but will get longer as I add more matches). The current match I am working on (where next is being shown) is 765

 

[Yongle]

If I have understood you correctly, your tables are SIDE-By-SIDE (in which case I do not need row numbers - in fact I should have asked for the column for team name in Table 2 - I have assumed it is in column J)

Conditional Formatting (delete previous rule)
I selected column J and used this as the CF formula
=COUNTIFS(A:A,J1,G:G,"Next")+COUNTIFS(B:B,J1,G:G,"Next")

Amend J to the column that contains team names in Table 2


Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Home	Away	H-Points	A-Points	H-Result	A-Result	Next Row		Rank	Team	Conf.
2​	Afghanistan	Albania	0.12​	0.62​	L	W			1​	Brazil	CONMEBOL
3​	Zimbabwe	Andorra	0.74​	0.07​	W	L			2​	Colombia	CONMEBOL
4​	Zambia	Angola	0.47​	0.22​	W	L			3​	Austria	Europe
5​	Yemen	Anguilla	0.98​	0​	W	L			4​	Chile	CONMEBOL
6​	Wales	Antigua & Barbuda	0.5​	0.7​	L	W			5​	Italy	Europe
7​	Albania	Angola	0.5​	0.7​	L	W			6​	Afghanistan	etc
8​	Afghanistan	Albania	0.5​	0.7​	L	W			7​	Zimbabwe
9​	Afghanistan	Albania	0.5​	0.7​	L	W			8​	Zambia
10​	Zimbabwe	Andorra	0.5​	0.7​	L	W			9​	Yemen
11​	Zambia	Angola	0.5​	0.7​	L	W			10​	Wales
12​	Yemen	Anguilla					Next		11​	Albania
13​	Wales	Antigua & Barbuda							12​	Andorra
14​	Albania	Angola							13​	Angola
15​	Afghanistan	Albania							14​	Anguilla
16​	Afghanistan	Albania							15​	Antigua & Barbuda

Sheet: Both Tables

 

[rackem12]

Yongle,

Thanks so much for the help. Worked like a charm!