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)
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,435
Office Version
  1. 2016
Platform
  1. Windows
Hi MSampson,

I've done as you did and used fixed cell references for the first row but subsequent are all calculated.
I've assumed column F is manually entered as you didn't describe how many samples could be calculated.
I've added a new column "Table Group" so I can keep track of which source row to use.
It calculates all other data down to row 9999 but you can change that range if required.

I checked results to your example and all matched.

MSampson.xlsx
BCDEFGHIJKL
2number of rows neededTable Group*col B interpolatedcol C interpolatedcol D interpolatedcol E interpolated
3194.56706786116.5211679136.122915416194.56706786116.5211679136.1229154
42.5103.3438736127.1744494148.25923026161.2596.02986881118.2967148138.1456345
55108.8686899136.0744065159.43314318161.597.49266976120.0722617140.1683536
610119.10105146.036832170.09282557161.7598.95547072121.8478087142.1910728
725130.5684167163.3527475189.8771711162100.4182717123.6233556144.2137919
850146.7740196185.1069233212.801690616162.25101.8810726125.3989025146.2365111
975165.0290936208.8683641235.575579418282.5103.3438736127.1744494148.2592302
1090184.8077731234.353515257.54843520282.8125104.0344756128.2869441149.6559693
1195201.3133789250.6737495272.651152817283.125104.7250776129.3994387151.0527084
1297.5215.7068058266.4703994284.466823814283.4375105.4156797130.5119334152.4494475
1399227.4731512284.6029525301.026537412283.75106.1062817131.624428153.8461867
14284.0625106.7968838132.7369226155.2429258
15284.375107.4874858133.8494173156.6396649
16284.6875108.1780878134.9619119158.036404
17375108.8686899136.0744065159.4331431
18375.71429110.3304556137.4976102160.9559549
19376.42857111.7922213138.9208138162.4787666
Sheet1
Cell Formulas
RangeFormula
H3H3=F4
I3:L3I3=B3
G4:G19G4=IF(ROW()-MATCH(MAX($G$3:G3),$G$3:G3,0)-1>INDEX($F$4:$F$9999,MAX($G$3:G3)),MAX($G$3:G3)+1,G3)
H4:H19H4=INDEX($F$4:$F$9999,MAX($G$3:G4))
I4:L19I4=IF($G3<>$G4,INDEX(B$3:B$9999,$G4),(((INDEX(B$3:B$9999,$G4+1)-(INDEX(B$3:B$9999,$G4)))/$H4))+I3)
F7:F13F7=ROUND(C7-C6,0)
 
Solution

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,435
Office Version
  1. 2016
Platform
  1. Windows
Hi MSampson,

Did the above work for you?

Just in case you want to know the last row number you must copy down the G4:L4 formulae I've put it in H1 and suppressed results if there's formulae below. I've also put the conditional format colors in as per your results.

MSampson.xlsx
BCDEFGHIJKL
1Last row=131
2number of rows neededTable Group*col B interpolatedcol C interpolatedcol D interpolatedcol E interpolated
3194.56706786116.5211679136.122915416194.56706786116.5211679136.1229154
42.5103.3438736127.1744494148.25923026161.2596.02986881118.2967148138.1456345
55108.8686899136.0744065159.43314318161.597.49266976120.0722617140.1683536
610119.10105146.036832170.09282557161.7598.95547072121.8478087142.1910728
725130.5684167163.3527475189.8771711162100.4182717123.6233556144.2137919
850146.7740196185.1069233212.801690616162.25101.8810726125.3989025146.2365111
975165.0290936208.8683641235.575579418282.5103.3438736127.1744494148.2592302
1090184.8077731234.353515257.54843520282.8125104.0344756128.2869441149.6559693
1195201.3133789250.6737495272.651152817283.125104.7250776129.3994387151.0527084
1297.5215.7068058266.4703994284.466823814283.4375105.4156797130.5119334152.4494475
1399227.4731512284.6029525301.026537412283.75106.1062817131.624428153.8461867
14284.0625106.7968838132.7369226155.2429258
15284.375107.4874858133.8494173156.6396649
16284.6875108.1780878134.9619119158.036404
17375108.8686899136.0744065159.4331431
18375.71429110.3304556137.4976102160.9559549
19376.42857111.7922213138.9208138162.4787666
Sheet1 (2)
Cell Formulas
RangeFormula
H1H1=SUM($F$3:$F$999)+ROW($H$2)
H3H3=F4
I3:L3I3=B3
G4:G19G4=IF(ROW()>$H$1,"",IF(ROW()-MATCH(MAX($G$3:G3),$G$3:G3,0)-1>INDEX($F$4:$F$9999,MAX($G$3:G3)),MAX($G$3:G3)+1,G3))
H4:H19H4=IF($G4="","",INDEX($F$4:$F$9999,MAX($G$3:G4)))
I4:L19I4=IF($G4="","",IF($G3<>$G4,INDEX(B$3:B$9999,$G4),(((INDEX(B$3:B$9999,$G4+1)-(INDEX(B$3:B$9999,$G4)))/$H4))+I3))
F7:F13F7=ROUND(C7-C6,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J3:L9999Expression=AND($G3<>$G2,$G3<>"")textNO
I3:I9999Expression=AND(G3<>G2,G3<>"")textNO
 

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
thank you! This works beautifully. I'm starting to learn to use index and match rather than just simple vlookups, but I'm still fumbling with them somewhat.

column F is simply the difference between the data in the rows of the original chart. There will be no data below B12:E12 because B are percentiles so this is the given max.

My next puzzle with this will be how to look at these results and retrieve more than just the first instance of a number in a column. With all these decimals they are different from each other, but I will unfortunately only have whole number to search for.
For example, when they are rounded to whole numbers J11 and J12 are both 105. I would then need to retrieve both row numbers so that I can pull row 9 and row 10 for further use.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,435
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You're welcome!

I'm not following the additional requirement so can you provide a worked example?
Maybe start at column N and dummy up the search you want and the cells you'd like to see?
 

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
thank you. I would be looking in Column C for a value that may occur more than once. A regular lookup will grab the first one it sees only. I need to grab all of them and all the columns to the right.

I made a mistake here. See below
 

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

the colors are just to highlight what I would expect to get.
 

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
oops, copied the wrong rows above.

sample.xlsx
BCDEFGHIJKLMNOPQRS
1
2number of rows neededtable group*col B interpolatedcol C interpolatedcol D interpolatedcol E interpolatedsearch col C for:return I from matchreturn J from matchreturn K from matchreturn L from match
3194.611713616194.6116.5136.11053.13105129151
42.5103.31271486161.25961181383.44105131152
55108.91361598161.5097120140
610119.11461707161.7599122142average of all matches3.28105130152
725130.616319011162.00100124144
850146.818521316162.25102125146
975165.020923618282.50103127148
1090184.823425820282.81104128150
1195201.325127317283.13105129151
1297.5215.726628414283.44105131152
1399227.528530112283.75106132154
14284.06107133155
15284.38107134157
16284.69108135158
17375.00109136159
18375.71110137161
19376.43112139162
20377.14113140164
21377.86115142166
22378.57116143167
23379.29118145169
2441110.00119146170
2541111.36120148172
2641112.73121149174
2741114.09122151175
2841115.45123152177
2941116.82124154179
3041118.18125155181
3141119.55126157183
3241120.91127159184
3341122.27128160186
3441123.64130162188
3551625.00131163190
3651626.56132165191
3751628.13133166193
3851629.69134167194
3951631.25135169196
4051632.81136170197
4151634.38137172198
Sheet1
Cell Formulas
RangeFormula
H3H3=F4
I3:L3I3=B3
P3:S4P3=I11
G4:G41G4=IF(ROW()-MATCH(MAX($G$3:G3),$G$3:G3,0)-1>INDEX($F$4:$F$9999,MAX($G$3:G3)),MAX($G$3:G3)+1,G3)
H4:H41H4=INDEX($F$4:$F$9999,MAX($G$3:G4))
I4:I41I4=IF($G3<>$G4,INDEX(B$3:B$13,$G4),(((INDEX(B$3:B$13,$G4+1)-(INDEX(B$3:B$13,$G4)))/$H4))+I3)
J4:L41J4=IF($G3<>$G4,INDEX(C$3:C$9999,$G4),(((INDEX(C$3:C$9999,$G4+1)-(INDEX(C$3:C$9999,$G4)))/$H4))+J3)
P6:S6P6=AVERAGE(P3:P4)
F7:F13F7=ROUND(C7-C6,0)
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,435
Office Version
  1. 2016
Platform
  1. Windows
Before I forget... If you want the last entry in column B to be reported then you'll need to enter "number of rows needed" into F14.

You say search in column C but you actually mean to search column J, right?

I see your data only ever has 2 rows containing the integer of any search criteria but I've allowed for 5 just in case. Add this section to the sheet:

MSampson.xlsx
OPQRS
2search col C for:return I from matchreturn J from matchreturn K from matchreturn L from match
31053.125104.7250776129.3994387151.0527084
43.4375105.4156797130.5119334152.4494475
5    
6    
7    
8
9average of all matches3.28125105.0703787129.955686151.751078
New1Feb
Cell Formulas
RangeFormula
P3:S7P3=IFERROR(INDEX(I$3:I$239,AGGREGATE(15,6,ROW($J$3:$J$239)-ROW($J$2)/(ROUND($J$3:$J$239,0)=$O$3),ROW()-ROW($P$2))),"")
P9:S9P9=AVERAGE(P3:P7)
 

msampson

Board Regular
Joined
Mar 9, 2004
Messages
124
Office Version
  1. 365
Platform
  1. MacOS
thank you!
now I get to try to teach myself to understand how aggregate, small, row etc. work.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,378
Messages
5,635,903
Members
416,887
Latest member
SheriE

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