Dynamically change a table with formulas

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
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
BCDEFGHIJK
1*Col E pulls from D and needs to drop down a row when F switches to the next row in the summary table
2number of rows needed*col B interpolatedcol C interpolatedcol D interpolatedcol E interpolated
3194.61171366194.6116.5136.1
42.5103.3127148661.2596.0118.3138.1
55108.9136159861.5097.5120.1140.2
610119.1146170761.7599.0121.8142.2
725130.61631901162.00100.4123.6144.2
850146.81852131662.25101.9125.4146.2
975165.02092361882.5103.3127.2148.3
1090184.82342582082.81104.0128.3149.7
1195201.32512731783.13104.7129.4151.1
1297.5215.72662841483.44105.4130.5152.4
1399227.52853011283.75106.1131.6153.8
1484.06106.8132.7155.2
1584.38107.5133.8156.6
1684.69108.2135.0158.0
1775108.9136.1159.4
1875.71110.3137.5161.0
1976.43111.8138.9162.5
2077.14113.3140.3164.0
2177.86114.7141.8165.5
2278.57116.2143.2167.0
2379.29117.6144.6168.6
241110119.1146.0170.1
251111.36120.1147.6171.9
261112.73121.2149.2173.7
271114.09122.2150.8175.5
281115.45123.3152.3177.3
Sheet1
Cell Formulas
RangeFormula
G3G3=F4
H3:K3H3=B3
G4G4=F4
H4:H8H4=(($B$4-$B$3)/$G3)+H3
I4:K8I4=((C$4-C$3)/$G4)+I3
G5G5=F4
G6G6=F4
G7G7=F4
G8:G9G8=F4
H9:K9H9=B4
G10G10=F5
H10:H16H10=(($B$5-$B$4)/$G9)+H9
I10:K16I10=((C$5-C$4)/$G10)+I9
G11G11=F5
G12G12=F5
G13G13=F5
G14G14=F5
G15G15=F5
G16:G17G16=F5
H17:K17H17=B5
G18G18=F6
H18:H23H18=(($B$6-$B$5)/$G17)+H17
I18:K23I18=((C$6-C$5)/$G18)+I17
G19G19=F6
G20G20=F6
G21G21=F6
G22G22=F6
G23:G24G23=F6
H24:K24H24=B6
G25G25=F7
H25:H28H25=(($B$7-$B$6)/$G24)+H24
I25:K28I25=((C$7-C$6)/$G25)+I24
G26G26=F7
G27G27=F7
G28G28=F7
F7:F13F7=ROUND(C7-C6,0)
 

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEFGHIJKLMNOPQRS
27178
28age18202530number of rows neededtable group18202530search col matching age for:return I from matchreturn J from matchreturn K from matchreturn L from match
2911211010911116112110109111126131.20125.70126.40129.10
30117116114118616113111110112match age132.40126.40126.80130.20
3112511911912481611411211111325    
32130125126128716115113111115    
331421321301391016115114112116    
341611681661693616116115113117
351901881861901928117116114118average of all matches131.80126.05126.60129.65
362142102072102328118116115119
372252222202241228119117115120
382382352332371328120117116120
392542512492531628121118117121
4025525225025415028122118117122
4128123118118123
4228124119118123
4337125119119124
4437126120120125
4537126121121125
4637127122122126
4737128122123126
4837129123124127
4937129124125127
50410130125126128
51410131126126129
52410132126127130
53410134127127131
54410135128128132
55410136129128134
56410137129128135
57410138130129136
58410140131129137
59410141131130138
60536142132130139
61536143133131140
62536143134132141
63536144135133142
64536144136134142
65536145137135143
66536145138136144
67536146139137145
Sheet1
Cell Formulas
RangeFormula
G27G27=F40+ROW(G28)
H29H29=F30
I29:L29I29=B29
P29:S33P29=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:G67G30=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:H67H30=INDEX($F$30:$F$10025,MAX($G$29:G30))
I30:I67I30=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:L67J30=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:S35P35=AVERAGE(P29:P33)
F34:F39F34=ROUND(C34-C33,0)
F40F40=SUM(F30:F39)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRST
27178
28age18202530number of rows neededtable group18202530search col matching age for:Row relative to row 28return I from matchreturn J from matchreturn K from matchreturn L from match
29112.0072110.3147108.9667111.074216112.0072110.3147108.9667111.074212622130125126128
30117116114118616112.8393111.2622109.8056112.2285match age23131.2125.7126.4129.1
31125119119124816113.6714112.2098110.6445113.382825     
32130125126128716114.5036113.1573111.4834114.5371     
331421321301391016115.3357114.1049112.3222115.6914     
34161168.3422166.4202169.28293616116.1679115.0524113.1611116.8457
35190.1684187.8001186.1697189.68031928117116114118average of all matches130.6125.35126.2128.55
36213.829210.4138206.9734209.6672328118116.375114.625118.75
37225.2744222.4919220.3476223.97011228119116.75115.25119.5
38237.6985235.0122233.1173236.9861328120117.125115.875120.25
39253.7162250.7711248.8551253.40571628121117.5116.5121
40254.7162251.7711249.8551254.405715028122117.875117.125121.75
4128123118.25117.75122.5
4228124118.625118.375123.25
4337125119119124
4437125.7143119.8571120124.5714
4537126.4286120.7143121125.1429
4637127.1429121.5714122125.7143
4737127.8571122.4286123126.2857
4837128.5714123.2857124126.8571
4937129.2857124.1429125127.4286
50410130125126128
51410131.2125.7126.4129.1
52410132.4126.4126.8130.2
53410133.6127.1127.2131.3
54410134.8127.8127.6132.4
55410136128.5128133.5
56410137.2129.2128.4134.6
57410138.4129.9128.8135.7
58410139.6130.6129.2136.8
59410140.8131.3129.6137.9
60536142132130139
61536142.5278133.0095131.0117139.8412
62536143.0556134.019132.0233140.6824
63536143.5833135.0285133.035141.5236
64536144.1111136.038134.0467142.3648
65536144.6389137.0475135.0584143.206
66536145.1667138.057136.07144.0471
67536145.6944139.0665137.0817144.8883
Sheet7
Cell Formulas
RangeFormula
G27G27=F40+ROW(G28)
P28P28="Row relative to row "&ROW($I$28)
H29H29=F30
I29:L29I29=B29
P29:P33P29=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:T30Q29=IFERROR(INDEX(I$29:I$265,$P29),"")
R31:T33R31=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:G67G30=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:H67H30=INDEX($F$30:$F$10025,MAX($G$29:G30))
I30:I67I30=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:L67J30=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:T35Q35=AVERAGE(Q29:Q33)
F34:F39F34=ROUND(C34-C33,0)
F40F40=SUM(F30:F39)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,233
Members
417,131
Latest member
Seanr19871

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
Top