Highlighting Specific Cells In Another Table

rackem12

New Member
Joined
Jun 21, 2017
Messages
11
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

<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>
[TD="class: xl63, width: 150"]Home
[/TD]
[TD="class: xl63, width: 150"]Away[/TD]
[TD="class: xl65, width: 62"]H-Points[/TD]
[TD="class: xl65, width: 62"]A-Points[/TD]
[TD="class: xl63, width: 62"]H-Result[/TD]
[TD="class: xl63, width: 62"]A-Result[/TD]

[TD="class: xl63"]Afghanistan
[/TD]
[TD="class: xl63"]Albania[/TD]
[TD="class: xl65"]0.12[/TD]
[TD="class: xl65"]0.62[/TD]
[TD="class: xl63"]L[/TD]
[TD="class: xl63"]W[/TD]

[TD="class: xl63"]Zimbabwe
[/TD]
[TD="class: xl63"]Andorra[/TD]
[TD="class: xl65"]0.74[/TD]
[TD="class: xl65"]0.07[/TD]
[TD="class: xl63"]W[/TD]
[TD="class: xl63"]L[/TD]

[TD="class: xl63"]Zambia
[/TD]
[TD="class: xl63"]Angola[/TD]
[TD="class: xl65"]0.47[/TD]
[TD="class: xl65"]0.22[/TD]
[TD="class: xl63"]W[/TD]
[TD="class: xl63"]L[/TD]

[TD="class: xl63"]Yemen
[/TD]
[TD="class: xl63"]Anguilla[/TD]
[TD="class: xl65"]0.98[/TD]
[TD="class: xl65"]0.00[/TD]
[TD="class: xl63"]W[/TD]
[TD="class: xl63"]L[/TD]

[TD="class: xl63"]Wales
[/TD]
[TD="class: xl63"]Antigua & Barbuda[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

</tbody>

Table 2

<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>
[TD="class: xl65, width: 38"]Rank
[/TD]
[TD="class: xl65, width: 137"]Team
[/TD]
[TD="class: xl65, width: 139"]Conf.[/TD]
[TD="class: xl65, width: 38"]GP[/TD]
[TD="class: xl65, width: 38"]W[/TD]
[TD="class: xl65, width: 22"]L[/TD]
[TD="class: xl65, width: 22"]T[/TD]
[TD="class: xl68, width: 38"]GB[/TD]
[TD="class: xl69, width: 48"]Win %[/TD]
[TD="class: xl67, width: 33"]PF[/TD]
[TD="class: xl67, width: 33"]PA[/TD]
[TD="class: xl67, width: 54"]PT[/TD]
[TD="class: xl67, width: 54"]PD[/TD]
[TD="class: xl70, width: 66"]Rtg[/TD]
[TD="class: xl70, width: 62"]RTG Diff[/TD]

[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]Brazil
[/TD]
[TD="class: xl65"]CONMEBOL[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl69"]1.000[/TD]
[TD="class: xl67"]5.41[/TD]
[TD="class: xl67"]0.10[/TD]
[TD="class: xl67"]0.49[/TD]
[TD="class: xl67"]5.31[/TD]
[TD="class: xl70"]1.0000[/TD]
[TD="class: xl70"][/TD]

[TD="class: xl65"]2[/TD]
[TD="class: xl65"]Colombia
[/TD]
[TD="class: xl65"]CONMEBOL[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl69"]1.000[/TD]
[TD="class: xl67"]5.33[/TD]
[TD="class: xl67"]0.14[/TD]
[TD="class: xl67"]0.53[/TD]
[TD="class: xl67"]5.19[/TD]
[TD="class: xl70"]0.9999[/TD]
[TD="class: xl70"]0.0000[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]Austria
[/TD]
[TD="class: xl65"]Europe[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl68"]-0.5[/TD]
[TD="class: xl69"]1.000[/TD]
[TD="class: xl67"]6.16[/TD]
[TD="class: xl67"]0.23[/TD]
[TD="class: xl67"]0.61[/TD]
[TD="class: xl67"]5.93[/TD]
[TD="class: xl70"]0.9999[/TD]
[TD="class: xl70"]0.0001[/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"]Chile[/TD]
[TD="class: xl65"]CONMEBOL[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl69"]1.000[/TD]
[TD="class: xl67"]5.05[/TD]
[TD="class: xl67"]0.23[/TD]
[TD="class: xl67"]0.72[/TD]
[TD="class: xl67"]4.82[/TD]
[TD="class: xl70"]0.9998[/TD]
[TD="class: xl70"]0.0001[/TD]

[TD="class: xl65"]5[/TD]
[TD="class: xl65"]Italy[/TD]
[TD="class: xl65"]Europe[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl68"][/TD]
[TD="class: xl69"]1.000[/TD]
[TD="class: xl67"]4.99[/TD]
[TD="class: xl67"]0.25[/TD]
[TD="class: xl67"]0.76[/TD]
[TD="class: xl67"]4.74[/TD]
[TD="class: xl70"]0.9997[/TD]
[TD="class: xl70"]0.0001[/TD]

</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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
1. Are the tables tabulated values ot structured tables created with Insert Table?
2. Are the tables in different sheets?
 
Upvote 0
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]Home[/td][td]Away[/td][td]H-Points[/td][td]A-Points[/td][td]H-Result[/td][td]A-Result[/td][td]Next Row[/td][td] Formula in Column G[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]Afghanistan[/td][td]Albania[/td][td]
0.12​
[/td][td]
0.62​
[/td][td]L[/td][td]W[/td][td][/td][td] =IF(AND(COUNTBLANK(C1:F1)=0,COUNTBLANK(C2:F2)>0),"Next","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]Zimbabwe[/td][td]Andorra[/td][td]
0.74​
[/td][td]
0.07​
[/td][td]W[/td][td]L[/td][td][/td][td] =IF(AND(COUNTBLANK(C2:F2)=0,COUNTBLANK(C3:F3)>0),"Next","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]Zambia[/td][td]Angola[/td][td]
0.47​
[/td][td]
0.22​
[/td][td]W[/td][td]L[/td][td][/td][td] =IF(AND(COUNTBLANK(C3:F3)=0,COUNTBLANK(C4:F4)>0),"Next","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]Yemen[/td][td]Anguilla[/td][td]
0.98​
[/td][td]
0​
[/td][td]W[/td][td]L[/td][td][/td][td] =IF(AND(COUNTBLANK(C4:F4)=0,COUNTBLANK(C5:F5)>0),"Next","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]Wales[/td][td]Antigua & Barbuda[/td][td]
0.5​
[/td][td]
0.7​
[/td][td]L[/td][td]W[/td][td][/td][td] =IF(AND(COUNTBLANK(C5:F5)=0,COUNTBLANK(C6:F6)>0),"Next","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]Albania[/td][td]Angola[/td][td][/td][td][/td][td][/td][td][/td][td]Next[/td][td] =IF(AND(COUNTBLANK(C6:F6)=0,COUNTBLANK(C7:F7)>0),"Next","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]Afghanistan[/td][td]Albania[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] =IF(AND(COUNTBLANK(C7:F7)=0,COUNTBLANK(C8:F8)>0),"Next","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Table 1[/td][/tr][/table]


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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]Rank[/td][td]Team[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]
1​
[/td][td]Brazil[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]
2​
[/td][td]Colombia[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]
3​
[/td][td]Austria[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]
4​
[/td][td]Chile[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]
5​
[/td][td]Italy[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]
6​
[/td][td]Afghanistan[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]
7​
[/td][td]Zimbabwe[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]
8​
[/td][td]Zambia[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td]
9​
[/td][td]Yemen[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]
10​
[/td][td]Wales[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12
[/td][td]
11​
[/td][td=bgcolor:#FF0000]Albania[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13
[/td][td]
12​
[/td][td]Andorra[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14
[/td][td]
13​
[/td][td=bgcolor:#FF0000]Angola[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15
[/td][td]
14​
[/td][td]Anguilla[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16
[/td][td]
15​
[/td][td]Antigua & Barbuda[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Table 2[/td][/tr][/table]
 
Upvote 0
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0
and the row numbers ???
Table 1 - Header row
Table 1 - Last row
Table 2 - Header row
 
Upvote 0
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
 
Upvote 0
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][th]
I
[/th][th]
J
[/th][th]
K
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td]Home[/td][td]Away[/td][td]H-Points[/td][td]A-Points[/td][td]H-Result[/td][td]A-Result[/td][td]Next Row[/td][td][/td][td]Rank[/td][td]Team[/td][td]Conf.[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]Afghanistan[/td][td]Albania[/td][td]
0.12​
[/td][td]
0.62​
[/td][td]L[/td][td]W[/td][td][/td][td][/td][td]
1​
[/td][td]Brazil[/td][td]CONMEBOL[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]Zimbabwe[/td][td]Andorra[/td][td]
0.74​
[/td][td]
0.07​
[/td][td]W[/td][td]L[/td][td][/td][td][/td][td]
2​
[/td][td]Colombia[/td][td]CONMEBOL[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]Zambia[/td][td]Angola[/td][td]
0.47​
[/td][td]
0.22​
[/td][td]W[/td][td]L[/td][td][/td][td][/td][td]
3​
[/td][td]Austria[/td][td]Europe[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]Yemen[/td][td]Anguilla[/td][td]
0.98​
[/td][td]
0​
[/td][td]W[/td][td]L[/td][td][/td][td][/td][td]
4​
[/td][td]Chile[/td][td]CONMEBOL[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]Wales[/td][td]Antigua & Barbuda[/td][td]
0.5​
[/td][td]
0.7​
[/td][td]L[/td][td]W[/td][td][/td][td][/td][td]
5​
[/td][td]Italy[/td][td]Europe[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]Albania[/td][td]Angola[/td][td]
0.5​
[/td][td]
0.7​
[/td][td]L[/td][td]W[/td][td][/td][td][/td][td]
6​
[/td][td]Afghanistan[/td][td]etc[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]Afghanistan[/td][td]Albania[/td][td]
0.5​
[/td][td]
0.7​
[/td][td]L[/td][td]W[/td][td][/td][td][/td][td]
7​
[/td][td]Zimbabwe[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]Afghanistan[/td][td]Albania[/td][td]
0.5​
[/td][td]
0.7​
[/td][td]L[/td][td]W[/td][td][/td][td][/td][td]
8​
[/td][td]Zambia[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td]Zimbabwe[/td][td]Andorra[/td][td]
0.5​
[/td][td]
0.7​
[/td][td]L[/td][td]W[/td][td][/td][td][/td][td]
9​
[/td][td=bgcolor:#FF0000]Yemen[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]Zambia[/td][td]Angola[/td][td]
0.5​
[/td][td]
0.7​
[/td][td]L[/td][td]W[/td][td][/td][td][/td][td]
10​
[/td][td]Wales[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12
[/td][td]Yemen[/td][td]Anguilla[/td][td][/td][td][/td][td][/td][td][/td][td]Next[/td][td][/td][td]
11​
[/td][td]Albania[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13
[/td][td]Wales[/td][td]Antigua & Barbuda[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
12​
[/td][td]Andorra[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14
[/td][td]Albania[/td][td]Angola[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
13​
[/td][td]Angola[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15
[/td][td]Afghanistan[/td][td]Albania[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
14​
[/td][td=bgcolor:#FF0000]Anguilla[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16
[/td][td]Afghanistan[/td][td]Albania[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
15​
[/td][td]Antigua & Barbuda[/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Both Tables[/td][/tr][/table]
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top