PDA

View Full Version : Solved: Hi Experts



rajkumar
09-01-2008, 09:19 AM
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
09-01-2008, 09:47 AM
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
09-01-2008, 09:47 AM
BTW, try and use at least some sort of meanigful thread title.

rajkumar
09-01-2008, 10:28 AM
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
09-01-2008, 10:32 AM
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
09-01-2008, 10:44 AM
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
09-01-2008, 03:05 PM
Where is your list of destinations stored? Just curious as I'm looking at this.

mikerickson
09-01-2008, 06:05 PM
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
09-02-2008, 12:54 AM
:beerchug: Cheers! man. Awesome.:clap:

Raj