Solved: Hi Experts

[rajkumar]

I have a claim format. Which has dropdowns populated with name of the places.

user need to choose one by one from starting point to end point.

What i want is to enter a formula in the total kms coulmn considering the matrix worksheet km table. I have attached the sample herewith.

Any help on this would be highly appreciated.

Thanks & Regards
Rajkumar

[Bob Phillips]

Do you mean that you want it to lookup the start and end point and find the mileage (kilometerage?) for that trip? If so, which start point, 1,2, 3 or what?

[Bob Phillips]

BTW, try and use at least some sort of meanigful thread title.

[rajkumar]

actually i want to sum km between startpoint 1 and 2 and 3 and 4 etc till end point and the result to appear in total kms by refering the matrix worksheet.

Raj:help

[Bob Phillips]

So if startpoint 1 is the office and startpoint 2 is home and startpoint 2 is Ajay Bsuiness Centre, is that 19 km (7 + 12) or just 12?

I think you should write up some examples. this could get conusing.

[rajkumar]

If startpoint 1 is home and starpoint 2 is office and starpoint 3 is ajay business center the sum km would be 0+7+12 = 19.

similarly st point 1 is home,2 is office, 3 is x and 4 is y etc

the result should be 0+7+ value of x in matrix table + value of y in matrix table.

The formula should sum up till the end point for non blank cells.

there are 9 startpoints and 1 end point in each row in the claim sheet, where user may use only 4 start point cells and 1 end point and leave 5 startpoint cells blank in a row.

hope this is clarifying

Raj :hi:

[grichey]

Where is your list of destinations stored? Just curious as I'm looking at this.

[mikerickson]

I tried to get an array function to work, but it didn't quite make it.

Add a new named range travelChart
= OFFSET(Matrix$A$1,0,0,COUNTA(Matrix!$A:$A),COUNTA(Matrix!$1:$1)

The array formula
=IF(ISNA(MATCH(D7:L7,travelrange,0)),0,INDEX(INDEX(travelChart,MATCH(C7:K7, travelrange,0)+1,),1,MATCH(D7:L7,travelrange,0)+1))
returns the values of the individual legs of the journey, but the array formula

=SUM(IF(ISNA(MATCH(D7:L7,travelrange,0)),0,INDEX(INDEX(travelChart,MATCH(C7 :K7,travelrange,0)+1,),1,MATCH(D7:L7,travelrange,0)+1)))
just multiplies the distance of the first leg by the number of legs.

I had to settle for two UDF's JourneyDistance(stopsRange)
and the one called by it, DistanceBetween(oneLocation,anotherLocation).

Putting =JourneyDistance(C7:L7) in M7 and dragging down gives you your total.
Note that in row 8, the empty StopPoint3 between stopPoint2 and StopPoint4 causes an #N/A error.

I mention the failed array formula approach because, as a general rule, native Excel is better than a UDF and that approach might be fixable.

[rajkumar]

:beerchug: Cheers! man. Awesome.:clap:

Raj