Solution (sort of) to repeating table headers

[JenniferMurphy]

I discovered a couple of ways to get repeating table headers that do not need the kludgy sequence numbers that Excel will add.

In this first solution, I add my own sequence numbers and then change the color of that character to the background color so that they are not visible. It is a bit of work, and you have to be careful that references are to the right section.

2022 NBA playoff odds.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	$100	Wager
5
6			5/04/22				Entering Round 2				Entering Round 1
7		Team	Moneyline Odds3	Rank3	Payout3 on $100	% Odds3	Moneyline Odds2	Rank2	Payout2 on $100	% Odds2	Moneyline Odds1	Rank1	Payout1 on $100	% Odds1
8		Phoenix Suns	+200	1	$300	33%	+320	2	$420	24%	+260	1	$360	28%
9		Golden State Warriors	+340	2	$440	23%	+300	1	$400	25%	+1000	4	$1,100	9%
10		Boston Celtics	+450	3	$550	18%	+375	3	$475	21%	+750	3	$850	12%
11		Miami Heat	+450	3	$550	18%	+500	4	$600	17%	+1000	4	$1,100	9%
12		Milwaukee Bucks	+600	5	$700	14%	+700	5	$800	13%	+500	2	$600	17%
13		Memphis Grizzlies	+1800	6	$1,900	5%	+1300	6	$1,400	7%	+1200	6	$1,300	8%
14		Philadelphia 76ers	+6600	7	$6,700	1%	+2500	8	$2,600	4%	+1600	7	$1,700	6%
15		Dallas Mavericks	+6600	7	$6,700	1%	+2000	7	$2,100	5%	+4000	8	$4,100	2%
NBA (1)

Cell Formulas
Range	Formula
E8:E15	E8	=RANK.EQ([@[Moneyline Odds3]],[Moneyline Odds3],1)
F8:F15	F8	=cvtodds([@[Moneyline Odds3]],"M","$")*Wager
G8:G15	G8	=cvtodds([@[Moneyline Odds3]],"M","%")
I8:I15	I8	=RANK.EQ([@[Moneyline Odds2]],[Moneyline Odds2],1)
J8:J15	J8	=cvtodds([@[Moneyline Odds2]],"M","$")*Wager
K8:K15	K8	=cvtodds([@[Moneyline Odds2]],"M","%")
M8:M15	M8	=RANK.EQ([@[Moneyline Odds1]],[Moneyline Odds1],1)
N8:N15	N8	=cvtodds([@[Moneyline Odds1]],"M","$")*Wager
O8:O15	O8	=cvtodds([@[Moneyline Odds1]],"M","%")

Named Ranges
Name	Refers To	Cells
'NBA (1)'!Wager	='NBA (1)'!$B$4	J8:J15, N8:N15, F8:F15

The CvtOdds function is a UDF I wrote.

Another solution it to make each section a separate table.

2022 NBA playoff odds.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
4	$100	Wager
5
6			Today (5/06/22)				Entering Round 2				Entering Round 1
7		Team	Moneyline Odds	Rank	Payout on $100	% Odds	Moneyline Odds	Rank	Payout on $100	% Odds	Moneyline Odds	Rank	Payout on $100	% Odds
8		Phoenix Suns	+200	1	$300	33%	+320	2	$420	24%	+260	1	$360	28%
9		Golden State Warriors	+340	2	$440	23%	+300	1	$400	25%	+1000	4	$1,100	9%
10		Boston Celtics	+450	3	$550	18%	+375	3	$475	21%	+750	3	$850	12%
11		Miami Heat	+450	3	$550	18%	+500	4	$600	17%	+1000	4	$1,100	9%
12		Milwaukee Bucks	+600	5	$700	14%	+700	5	$800	13%	+500	2	$600	17%
13		Memphis Grizzlies	+1800	6	$1,900	5%	+1300	6	$1,400	7%	+1200	6	$1,300	8%
14		Philadelphia 76ers	+6600	7	$6,700	1%	+2500	8	$2,600	4%	+1600	7	$1,700	6%
15		Dallas Mavericks	+6600	7	$6,700	1%	+2000	7	$2,100	5%	+4000	8	$4,100	2%
NBA

Cell Formulas
Range	Formula
E8:E15,M8:M15,I8:I15	E8	=RANK.EQ([@[Moneyline Odds]],[Moneyline Odds],1)
F8:F15,N8:N15,J8:J15	F8	=cvtodds([@[Moneyline Odds]],"M","$")*Wager
G8:G15,O8:O15,K8:K15	G8	=cvtodds([@[Moneyline Odds]],"M","%")

Named Ranges
Name	Refers To	Cells
Wager	=NBA!$B$4	J8:J15, N8:N15, F8:F15

Each one has advantages.

Any comments?

 

[JenniferMurphy]

Making each section a separate table has too many disadvantages. The "hidden" header sequence numbers method works better, but I didn't like that they caused the headers to not be centered properly. My solution is to put the sequence number on the left followed by a line feed (Alt+Enter). This has the added advantage of adding more leading. The minisheet is below. I'm including a screen capture to show how it really looks.

And here is the minisheet.

2022 NBA playoff odds.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
5	$100	Wager
6
7		"ML" = MoneyLine odds Payout is on $100 wager	May 9, 2022 Warriors Up 2-1					May 5, 2022 Series Tied 1-1					Entering Round 2 vs Memphis #2 Warriors Beat Denver 4-1					Entering Round 1 Warriors #3 vs Denver #6
8		Team	4 ML	4 Rank	4 Δ	4 Payout	4 Odds	3 ML	3 Rank	3 Δ	3 Payout	3 Odds	2 ML	2 Rank	2 Δ	2 Payout	2 Odds	1 ML	1 Rank	1 Payout	1 Odds
9		Golden State Warriors	+222	1	+1	$322	31%	+340	2	-1	$440	23%	+300	1	+3	$400	25%	+1000	4	$1,100	9%
10		Phoenix Suns	+300	2	-1	$400	25%	+200	1	+1	$300	33%	+320	2	-1	$420	24%	+260	1	$360	28%
11		Milwaukee Bucks	+450	3	+2	$550	18%	+600	5	=0	$700	14%	+700	5	-3	$800	13%	+500	2	$600	17%
12		Miami Heat	+650	4	-1	$750	13%	+450	3	+1	$550	18%	+500	4	=0	$600	17%	+1000	4	$1,100	9%
13		Boston Celtics	+700	5	-2	$800	13%	+450	3	=0	$550	18%	+375	3	=0	$475	21%	+750	3	$850	12%
14		Philadelphia 76ers	+1148	6	+1	$1,248	8%	+6600	7	+1	$6,700	1%	+2500	8	-1	$2,600	4%	+1600	7	$1,700	6%
15		Dallas Mavericks	+2000	7	=0	$2,100	5%	+6600	7	=0	$6,700	1%	+2000	7	+1	$2,100	5%	+4000	8	$4,100	2%
16		Memphis Grizzlies	+3500	8	-2	$3,600	3%	+1800	6	=0	$1,900	5%	+1300	6	=0	$1,400	7%	+1200	6	$1,300	8%
NBA

Cell Formulas
Range	Formula
E9:E16	E9	=RANK.EQ([@[4 ML]], [4 ML] ,1)
F9:F16	F9	=[@[3 Rank]]-[@[4 Rank]]
G9:G16	G9	=cvtodds([@[4 ML]],"M","$")*Wager
H9:H16	H9	=cvtodds([@[4 ML]],"M","%")
J9:J16	J9	=RANK.EQ([@[3 ML]],[3 ML],1)
K9:K16	K9	=[@[2 Rank]]-[@[3 Rank]]
L9:L16	L9	=cvtodds([@[3 ML]],"M","$")*Wager
M9:M16	M9	=cvtodds([@[3 ML]],"M","%")
O9:O16	O9	=RANK.EQ([@[2 ML]],[2 ML],1)
P9:P16	P9	=[@[1 Rank]]-[@[2 Rank]]
Q9:Q16	Q9	=cvtodds([@[2 ML]],"M","$")*Wager
R9:R16	R9	=cvtodds([@[2 ML]],"M","%")
T9:T16	T9	=RANK.EQ([@[1 ML]],[1 ML],1)
U9:U16	U9	=cvtodds([@[1 ML]],"M","$")*Wager
V9:V16	V9	=cvtodds([@[1 ML]],"M","%")

Named Ranges
Name	Refers To	Cells
Wager	=NBA!$B$5	G9:G16, U9:U16, Q9:Q16, L9:L16

 

[JenniferMurphy]

Here's another example from the finals. Sadly, my Warriors got whupped in game 1 and lost home court advantage , but are still favored.