Dynamically change a table with formulas

[msampson]

I have a short summary table. I am trying to stretch it out to fill in blanks by interpolating between the existing cells.
I have a variable number of rows needed between each row of the summary table to the extended table. I am trying to make this with formulas so that when I switch to a different summary table it will adjust the number of rows between each and automatically drop down to the next row of the summary table for all the needed formulas across the extended table.
Another way to try to explain it is I'm using $ to fix cell positions in, say, 6 rows, but when I get to the next row in the summary I'll need to increment those $ down one to apply the formulas to the next row down. An then use that $ in, say 8 rows, before switching down again. I've uploaded a minisheet that does what I want to try to show it better.

sample.xlsx
	B	C	D	E	F	G	H	I	J	K
1	*	Col E pulls from D and needs to drop down a row when F switches to the next row in the summary table
2					number of rows needed	*	col B interpolated	col C interpolated	col D interpolated	col E interpolated
3	1	94.6	117	136		6	1	94.6	116.5	136.1
4	2.5	103.3	127	148	6	6	1.25	96.0	118.3	138.1
5	5	108.9	136	159	8	6	1.50	97.5	120.1	140.2
6	10	119.1	146	170	7	6	1.75	99.0	121.8	142.2
7	25	130.6	163	190	11	6	2.00	100.4	123.6	144.2
8	50	146.8	185	213	16	6	2.25	101.9	125.4	146.2
9	75	165.0	209	236	18	8	2.5	103.3	127.2	148.3
10	90	184.8	234	258	20	8	2.81	104.0	128.3	149.7
11	95	201.3	251	273	17	8	3.13	104.7	129.4	151.1
12	97.5	215.7	266	284	14	8	3.44	105.4	130.5	152.4
13	99	227.5	285	301	12	8	3.75	106.1	131.6	153.8
14						8	4.06	106.8	132.7	155.2
15						8	4.38	107.5	133.8	156.6
16						8	4.69	108.2	135.0	158.0
17						7	5	108.9	136.1	159.4
18						7	5.71	110.3	137.5	161.0
19						7	6.43	111.8	138.9	162.5
20						7	7.14	113.3	140.3	164.0
21						7	7.86	114.7	141.8	165.5
22						7	8.57	116.2	143.2	167.0
23						7	9.29	117.6	144.6	168.6
24						11	10	119.1	146.0	170.1
25						11	11.36	120.1	147.6	171.9
26						11	12.73	121.2	149.2	173.7
27						11	14.09	122.2	150.8	175.5
28						11	15.45	123.3	152.3	177.3
Sheet1

Cell Formulas
Range	Formula
G3	G3	=F4
H3:K3	H3	=B3
G4	G4	=F4
H4:H8	H4	=(($B$4-$B$3)/$G3)+H3
I4:K8	I4	=((C$4-C$3)/$G4)+I3
G5	G5	=F4
G6	G6	=F4
G7	G7	=F4
G8:G9	G8	=F4
H9:K9	H9	=B4
G10	G10	=F5
H10:H16	H10	=(($B$5-$B$4)/$G9)+H9
I10:K16	I10	=((C$5-C$4)/$G10)+I9
G11	G11	=F5
G12	G12	=F5
G13	G13	=F5
G14	G14	=F5
G15	G15	=F5
G16:G17	G16	=F5
H17:K17	H17	=B5
G18	G18	=F6
H18:H23	H18	=(($B$6-$B$5)/$G17)+H17
I18:K23	I18	=((C$6-C$5)/$G18)+I17
G19	G19	=F6
G20	G20	=F6
G21	G21	=F6
G22	G22	=F6
G23:G24	G23	=F6
H24:K24	H24	=B6
G25	G25	=F7
H25:H28	H25	=(($B$7-$B$6)/$G24)+H24
I25:K28	I25	=((C$7-C$6)/$G25)+I24
G26	G26	=F7
G27	G27	=F7
G28	G28	=F7
F7:F13	F7	=ROUND(C7-C6,0)

 

[msampson]

This expands on one from before. Searching for 126 (highlighted in green) will return different rows depending on which age (row 28) column is searched.

sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
27							178
28	age	18	20	25	30	number of rows needed	table group		18	20	25	30			search col matching age for:	return I from match	return J from match	return K from match	return L from match
29		112	110	109	111		1	6	112	110	109	111			126	131.20	125.70	126.40	129.10
30		117	116	114	118	6	1	6	113	111	110	112			match age	132.40	126.40	126.80	130.20
31		125	119	119	124	8	1	6	114	112	111	113			25
32		130	125	126	128	7	1	6	115	113	111	115
33		142	132	130	139	10	1	6	115	114	112	116
34		161	168	166	169	36	1	6	116	115	113	117
35		190	188	186	190	19	2	8	117	116	114	118			average of all matches	131.80	126.05	126.60	129.65
36		214	210	207	210	23	2	8	118	116	115	119
37		225	222	220	224	12	2	8	119	117	115	120
38		238	235	233	237	13	2	8	120	117	116	120
39		254	251	249	253	16	2	8	121	118	117	121
40		255	252	250	254	150	2	8	122	118	117	122
41							2	8	123	118	118	123
42							2	8	124	119	118	123
43							3	7	125	119	119	124
44							3	7	126	120	120	125
45							3	7	126	121	121	125
46							3	7	127	122	122	126
47							3	7	128	122	123	126
48							3	7	129	123	124	127
49							3	7	129	124	125	127
50							4	10	130	125	126	128
51							4	10	131	126	126	129
52							4	10	132	126	127	130
53							4	10	134	127	127	131
54							4	10	135	128	128	132
55							4	10	136	129	128	134
56							4	10	137	129	128	135
57							4	10	138	130	129	136
58							4	10	140	131	129	137
59							4	10	141	131	130	138
60							5	36	142	132	130	139
61							5	36	143	133	131	140
62							5	36	143	134	132	141
63							5	36	144	135	133	142
64							5	36	144	136	134	142
65							5	36	145	137	135	143
66							5	36	145	138	136	144
67							5	36	146	139	137	145
Sheet1

Cell Formulas
Range	Formula
G27	G27	=F40+ROW(G28)
H29	H29	=F30
I29:L29	I29	=B29
P29:S33	P29	=IFERROR(INDEX(I$29:I$265,AGGREGATE(15,6,ROW($J$29:$J$265)-ROW($J$28)/(ROUND($J$29:$J$265,0)=$O$29),ROW()-ROW($P$28))),"")
G30:G67	G30	=IF(ROW()>$G$27,"",IF(ROW()-MATCH(MAX($G$29:G29),$G$29:G29,0)-ROW($G$28)+1>INDEX($F$30:$F$39,MAX($G$29:G29)),MAX($G$29:G29)+1,G29))
H30:H67	H30	=INDEX($F$30:$F$10025,MAX($G$29:G30))
I30:I67	I30	=IF($G29<>$G30,INDEX(B$29:B$39,$G30),(((INDEX(B$29:B$39,$G30+1)-(INDEX(B$29:B$39,$G30)))/$H30))+I29)
J30:L67	J30	=IF($G29<>$G30,INDEX(C$29:C$10025,$G30),(((INDEX(C$29:C$10025,$G30+1)-(INDEX(C$29:C$10025,$G30)))/$H30))+J29)
P35:S35	P35	=AVERAGE(P29:P33)
F34:F39	F34	=ROUND(C34-C33,0)
F40	F40	=SUM(F30:F39)

 

[Toadstool]

The formulae were looking a little too complex so I've cheated by adding a worker column to give the rows concerned and then the actual retrieval columns Q to T become a simple INDEX.

MSampson2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
27							178
28	age	18	20	25	30	number of rows needed	table group		18	20	25	30			search col matching age for:	Row relative to row 28	return I from match	return J from match	return K from match	return L from match
29		112.0072	110.3147	108.9667	111.0742		1	6	112.0072	110.3147	108.9667	111.0742			126	22	130	125	126	128
30		117	116	114	118	6	1	6	112.8393	111.2622	109.8056	112.2285			match age	23	131.2	125.7	126.4	129.1
31		125	119	119	124	8	1	6	113.6714	112.2098	110.6445	113.3828			25
32		130	125	126	128	7	1	6	114.5036	113.1573	111.4834	114.5371
33		142	132	130	139	10	1	6	115.3357	114.1049	112.3222	115.6914
34		161	168.3422	166.4202	169.2829	36	1	6	116.1679	115.0524	113.1611	116.8457
35		190.1684	187.8001	186.1697	189.6803	19	2	8	117	116	114	118			average of all matches		130.6	125.35	126.2	128.55
36		213.829	210.4138	206.9734	209.667	23	2	8	118	116.375	114.625	118.75
37		225.2744	222.4919	220.3476	223.9701	12	2	8	119	116.75	115.25	119.5
38		237.6985	235.0122	233.1173	236.986	13	2	8	120	117.125	115.875	120.25
39		253.7162	250.7711	248.8551	253.4057	16	2	8	121	117.5	116.5	121
40		254.7162	251.7711	249.8551	254.4057	150	2	8	122	117.875	117.125	121.75
41							2	8	123	118.25	117.75	122.5
42							2	8	124	118.625	118.375	123.25
43							3	7	125	119	119	124
44							3	7	125.7143	119.8571	120	124.5714
45							3	7	126.4286	120.7143	121	125.1429
46							3	7	127.1429	121.5714	122	125.7143
47							3	7	127.8571	122.4286	123	126.2857
48							3	7	128.5714	123.2857	124	126.8571
49							3	7	129.2857	124.1429	125	127.4286
50							4	10	130	125	126	128
51							4	10	131.2	125.7	126.4	129.1
52							4	10	132.4	126.4	126.8	130.2
53							4	10	133.6	127.1	127.2	131.3
54							4	10	134.8	127.8	127.6	132.4
55							4	10	136	128.5	128	133.5
56							4	10	137.2	129.2	128.4	134.6
57							4	10	138.4	129.9	128.8	135.7
58							4	10	139.6	130.6	129.2	136.8
59							4	10	140.8	131.3	129.6	137.9
60							5	36	142	132	130	139
61							5	36	142.5278	133.0095	131.0117	139.8412
62							5	36	143.0556	134.019	132.0233	140.6824
63							5	36	143.5833	135.0285	133.035	141.5236
64							5	36	144.1111	136.038	134.0467	142.3648
65							5	36	144.6389	137.0475	135.0584	143.206
66							5	36	145.1667	138.057	136.07	144.0471
67							5	36	145.6944	139.0665	137.0817	144.8883
Sheet7

Cell Formulas
Range	Formula
G27	G27	=F40+ROW(G28)
P28	P28	="Row relative to row "&ROW($I$28)
H29	H29	=F30
I29:L29	I29	=B29
P29:P33	P29	=IFERROR(AGGREGATE(15,6,ROW($J$29:$J$265)-ROW($J$28)/(ROUND(OFFSET($I$29,,MATCH($O$31,$I$28:$L$28,0)-1,ROWS($I$29:$I$265)),0)=$O$29),ROW()-ROW($Q$28)),"")
Q31:Q33,Q29:T30	Q29	=IFERROR(INDEX(I$29:I$265,$P29),"")
R31:T33	R31	=IFERROR(INDEX(J$29:J$265,AGGREGATE(15,6,ROW($J$29:$J$265)-ROW($J$28)/(ROUND($J$29:$J$265,0)=$O$29),ROW()-ROW($Q$28))),"")
G30:G67	G30	=IF(ROW()>$G$27,"",IF(ROW()-MATCH(MAX($G$29:G29),$G$29:G29,0)-ROW($G$28)+1>INDEX($F$30:$F$39,MAX($G$29:G29)),MAX($G$29:G29)+1,G29))
H30:H67	H30	=INDEX($F$30:$F$10025,MAX($G$29:G30))
I30:I67	I30	=IF($G29<>$G30,INDEX(B$29:B$39,$G30),(((INDEX(B$29:B$39,$G30+1)-(INDEX(B$29:B$39,$G30)))/$H30))+I29)
J30:L67	J30	=IF($G29<>$G30,INDEX(C$29:C$10025,$G30),(((INDEX(C$29:C$10025,$G30+1)-(INDEX(C$29:C$10025,$G30)))/$H30))+J29)
Q35:T35	Q35	=AVERAGE(Q29:Q33)
F34:F39	F34	=ROUND(C34-C33,0)
F40	F40	=SUM(F30:F39)