Formulas to Return Information Amongst Various Cells

[PivotTablePSHomage]

I combined a few reports in Adobe, and then saved the file as an Excel file. To present a hypothetical example divulging or intimating the actual information on these files:

The files provide a guide to property for sale.

The files provide data on:

Section I:
1a: Date of Construction of Home
1b: Last Consecutively Previous Renovation
1c: Number of Renovations in the Last Ten Years
2a: Size of Home
2b: Width of Roof
3a: Width of Attic
3b: Height of Attic
4a: Width of Basement
4b: Height of Basement
5a: Number of Rooms
5b: Number of Bathrooms
6a: Number of Windows
6: Number of French Windows

Section II:
1(a): Distance to Closest Police Station
1(b): Phone Number for Police Station for Other Than Emergency Calls
2(a): Distance to Closest Fire Station
2(b): Distance to Closest Hospital
3(a) Size of Yard
3(b) Other Items on Curtilage
4(a) Size of Garage
4(b) Size of Driveway
5(a) Number of Trees on Curtilage
5(b) Mailbox on Curtilage
5(c) Mailbox on Edifice
6(a) Castle Doctrine
6(b) Imperfect Self-Defense
7a: Last Resident
7b: Length of Residence of Last Resident
8a: Previous to Last Resident
8b: Length of Residence of Last Resident
9a: Number of Insurance Claims on Home
9b: Number of Insurance Claims in Last Ten Years
10a: Number of Insect Infestation Incidents Reported
10b: Number of Cockroach Infestations Reported
10c: Number of Termite Infestations Reported
11a: Number of Fires Reported
11b: Number of Fires Caused by Lightning
12a: Seismic Activity Incidents Reported
12b: Incidents of Serious Damage Reported Due to Seismic Activity

The Excel version of the Adobe file provided a separate cell for, for example, 5a, and then populated Number of Trees on Curtilage in a cell to the right, followed by a third cell in this section populating the Number of Trees on Curtilage.

I wanted to set up an Excel formula that would retrieve the Number of Trees on Curtilage for the various separate properties and do so on one sheet. I wanted to prevent various blank cells, just to retrieve the data from the adjacent cells to the cells that populated Number of Trees on Curtilage.

The FILTER function seems helpful. However, I prefer to have additional methods.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[PivotTablePSHomage]

I uploaded a file as an example of the situation. Proceed with suggested formulas/formule.

[PivotTablePSHomage]

I think I finally figured the situation.

To make a dynamic, scalable range:

=MATCH(B1,Info!B1:B5000,0)
=MATCH(B2,INDIRECT("Info!"&"B"&(C1+1)&":"&"B5000"),0)+C1
=MATCH(B3,INDIRECT("Info!"&"B"&(C2+1)&":"&"B5000"),0)+C2
=MATCH(B4,INDIRECT("Info!"&"B"&(C3+1)&":"&"B5000"),0)+C3
_______________________________________________________________________________________________________________________________________________________

Property 212 Mailbox on Curtilage (Outside of Edifice) 27
Property 213 Mailbox on Curtilage (Outside of Edifice) 71
Property 214 Mailbox on Curtilage (Outside of Edifice) 115
Property 215 Mailbox on Curtilage (Outside of Edifice) 159

Property 212
Section I
1a Date of Construction of Home 141.9723047
1b Last Consecutively Previous Renovation 66.98406068
1c Number of Renovations in the Last Ten Years 135.3128855
2a Size of Home 170.5292678
2b Width of Roof 16.25780612
3a Width of Attic 10.45486753
3b Height of Attic 73.37464437
4a Width of Basement 33.38275112
4b Height of Basement 113.7314521
5a Number of Rooms 64.17721493
5b Number of Bathrooms 160.0692689
6a Number of Windows 58.4377489
6b Number of French Windows 84.64861106

Section II
1a Distance to Closest Police Station 105.2509456
1b Phone Number for Police Station for Other Than Emergency Calls 6.953019358
2a Distance to Closest Fire Station 148.8688407
2b Distance to Closest Hospital 11.63354487
3a Size of Yard 93.75439007
3b Other Items on Curtilage 19.57986109
4a Size of Garage 59.72241059
4b Size of Driveway 41.44149189
5a Number of Trees on Curtilage 39.1558297
5b Mailbox on Curtilage (Outside of Edifice) 103.2250136
5c Mailbox on Edifice 139.2710713
6a Castle Doctrine 131.8408256
6b Imperfect Self-Defense 110.373344
7a Last Resident 129.0207166
7b Length of Residence of Last Resident 125.6734218
8a Previous to Last Resident 135.859781
8b Length of Residence of Last Resident 107.9666839
9a Number of Insurance Claims on Home 64.67970341
9b Number of Insurance Claims in Last Ten Years 172.3705617
10a Number of Insect Infestation Incidents Reported 41.25091287
10b Number of Cockroach Infestations Reported 102.8700727
10c Number of Termite Infestations Reported 155.9143963
11a Number of Fires Reported 10.48137626
11b Number of Fires Caused by Lightning 34.33611254
12a Seismic Activity Incidents Reported 92.01331921
12b Incidents of Serious Damage Reported Due to Seismic Activity 79.38939523

Property 213
Section I 3.331817042
1a Date of Construction of Home 144.6226933
1b Last Consecutively Previous Renovation 37.74179966
1c Number of Renovations in the Last Ten Years 75.00934445
2a Size of Home 91.51147151
2b Width of Roof 88.1846081
3a Width of Attic 130.3038192
3b Height of Attic 166.0702978
4a Width of Basement 13.48926594
4b Height of Basement 69.81575303
5a Number of Rooms 124.7274623
5b Number of Bathrooms 131.8262467
6a Number of Windows 121.2009172
6b Number of French Windows 41.55091717

Section II 44.91038282
1a Distance to Closest Police Station 123.683659
1b Phone Number for Police Station for Other Than Emergency Calls 150.7262748
2a Distance to Closest Fire Station 55.08164411
2b Distance to Closest Hospital 122.297179
3a Size of Yard 148.5346314
3b Other Items on Curtilage 173.4161061
4a Size of Garage 68.37609108
4b Size of Driveway 5.927793684
5a Number of Trees on Curtilage 14.83231585
5b Mailbox on Curtilage (Outside of Edifice) 109.9651537
5c Mailbox on Edifice 123.7859267
6a Castle Doctrine 20.49160954
6b Imperfect Self-Defense 12.94459147
7a Last Resident 108.5163003
7b Length of Residence of Last Resident 79.80496307
8a Previous to Last Resident 119.5705863
8b Length of Residence of Last Resident 66.47530811
9a Number of Insurance Claims on Home 115.6009992
9b Number of Insurance Claims in Last Ten Years 138.8222039
10a Number of Insect Infestation Incidents Reported 66.35804461
10b Number of Cockroach Infestations Reported 10.64114232
10c Number of Termite Infestations Reported 141.3269237
11a Number of Fires Reported 31.90724295
11b Number of Fires Caused by Lightning 157.5986596
12a Seismic Activity Incidents Reported 104.8911172
12b Incidents of Serious Damage Reported Due to Seismic Activity 45.70452484

Property 214
Section I
1a Date of Construction of Home 20.34027734
1b Last Consecutively Previous Renovation 163.3261367
1c Number of Renovations in the Last Ten Years 171.5591577
2a Size of Home 19.56399915
2b Width of Roof 129.0770471
3a Width of Attic 103.915099
3b Height of Attic 158.8881059
4a Width of Basement 9.635510768
4b Height of Basement 121.235428
5a Number of Rooms 108.9815559
5b Number of Bathrooms 74.15888269
6a Number of Windows 107.8523153
6b Number of French Windows 54.204543

Section II
1a Distance to Closest Police Station 173.2533321
1b Phone Number for Police Station for Other Than Emergency Calls 136.6134082
2a Distance to Closest Fire Station 40.75981116
2b Distance to Closest Hospital 18.11618325
3a Size of Yard 50.57670505
3b Other Items on Curtilage 20.34829439
4a Size of Garage 122.9379928
4b Size of Driveway 140.0757107
5a Number of Trees on Curtilage 105.3430766
5b Mailbox on Curtilage (Outside of Edifice) 13.73933837
5c Mailbox on Edifice 92.83618032
6a Castle Doctrine 108.9724452
6b Imperfect Self-Defense 57.31467417
7a Last Resident 78.76035491
7b Length of Residence of Last Resident 101.6620375
8a Previous to Last Resident 37.36329309
8b Length of Residence of Last Resident 74.89157228
9a Number of Insurance Claims on Home 33.02684224
9b Number of Insurance Claims in Last Ten Years 159.1353791
10a Number of Insect Infestation Incidents Reported 42.70199509
10b Number of Cockroach Infestations Reported 93.7786657
10c Number of Termite Infestations Reported 77.54188815
11a Number of Fires Reported 170.0986173
11b Number of Fires Caused by Lightning 107.7642434
12a Seismic Activity Incidents Reported 120.2627931
12b Incidents of Serious Damage Reported Due to Seismic Activity 26.31702984

Property 215
Section I 119.7403343
1a Date of Construction of Home 8.846315319
1b Last Consecutively Previous Renovation 160.9612916
1c Number of Renovations in the Last Ten Years 108.3312189
2a Size of Home 7.504724783
2b Width of Roof 87.27279588
3a Width of Attic 86.11810297
3b Height of Attic 95.72124602
4a Width of Basement 107.1825239
4b Height of Basement 16.93669731
5a Number of Rooms 70.20925829
5b Number of Bathrooms 116.5464096
6a Number of Windows 13.97617446
6b Number of French Windows 72.63637596

Section II
1a Distance to Closest Police Station 0.914066626
1b Phone Number for Police Station for Other Than Emergency Calls 107.6871882
2a Distance to Closest Fire Station 31.28854986
2b Distance to Closest Hospital 74.39983951
3a Size of Yard 68.31689543
3b Other Items on Curtilage 63.17378983
4a Size of Garage 155.8513237
4b Size of Driveway 74.24948155
5a Number of Trees on Curtilage 0.009228669
5b Mailbox on Curtilage (Outside of Edifice) 145.364677
5c Mailbox on Edifice 5.151087969
6a Castle Doctrine 34.63239404
6b Imperfect Self-Defense 61.39251986
7a Last Resident 50.90185184
7b Length of Residence of Last Resident 6.775388061
8a Previous to Last Resident 19.05895957
8b Length of Residence of Last Resident 133.9852894
9a Number of Insurance Claims on Home 94.31754517
9b Number of Insurance Claims in Last Ten Years 25.62787512
10a Number of Insect Infestation Incidents Reported 120.3228772
10b Number of Cockroach Infestations Reported 64.48230762
10c Number of Termite Infestations Reported 71.32308121
11a Number of Fires Reported 153.2236107
11b Number of Fires Caused by Lightning 69.34524585
12a Seismic Activity Incidents Reported 86.68403212
12b Incidents of Serious Damage Reported Due to Seismic Activity 135.5915894