# Excel Formula to Auto-populate Start & End Values in Datasheet

#### Mr Engineer

##### New Member
Hi there

Context of problem: I am trying to quantify lengths of fencing along a road into excel. I type the extents of the fencing into Table 1.

Note that Start & End represents the location on the road. So for example, a fencing which starts at location "90" and runs to location "95" is 5 meters long.

I have a data table, which represents the area in 20 meter blocks.

Problem: I am trying to auto populate the total lengths of fencing into Column G. In the picture, I have manually entered these in, but I need a formula which can automate this.

Note I need to account for the overlap in the fencing between locations "80" to "100".

Can anybody give me a hand?

Thanks
Mr. Engineer

#### StephenCrump

##### Well-known Member
Like this perhaps:

<table1[end])*(f3>G3 (array entered): {=SUMPRODUCT((E3< Table1[End])*(F3>Table1[Start]),IF(F3>Table1[End],Table1[End],F3)-IF(E3>Table1[Start],E3,Table1[Start]))}

Excel 2010
ABCDEFG
1Table 1Data Table
2StartEndLengthStartEndLength
35587320200
49095520400
5101102140605
61041062608020
711011448010012
8119132131001208
91481671912014012
10185187214016012
112003311311601807
121802002
1320920022020
1422024020
1524026020
1626028020
1728030020
1830032020
1932034011
203403600
213603800
22
23209

<tbody>
</tbody>
Sheet1

</table1[end])*(f3>

1,082,246
Messages
5,363,993
Members
400,772
Latest member
solbebe

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...