Drop Downs and associated Formula

Need a little help please

See attached sample. I have my lists of routes and their associated junctions and distances on the 'Lists' worksheet.

On the 'Database' work sheet:
1. Column A is to have a drop down list for the Routes
2. Columns B & C are to have drop down lists for the junctions based on the route selected in Column A
3. Column D is to have an automated formula showing the distance between the selected junctions

I have shown an example on the attached.

Also, i need to know how to show only positive values on Column D, in the event that it results in a negative value (as i could be travelling in the other direction).

Please bear in mind I do not have Kutools. Do I need it to do this?

Thanks a mill

Welcome to the forum.

Please update your user profile with the VERSION of Excel that you are using.

The attached solution makes use of:

1. Named ranges for the junctions on each route (_A5, _M3, _N1).
2. A data validation list formula for the drop-down: =INDIRECT("_"&$A3).
3. Named ranges for the lookup tables (RouteA5, RouteM3, RouteN1).
4. The following calculation formula: =ABS(VLOOKUP(C3,INDIRECT("Route"&A3),2,0)-VLOOKUP(B3,INDIRECT("Route"&A3),2,0)).

Let me know if you need any help with it.

In B1 of lists sheet

="Lists!"&CELL("address",B3)&":"&CELL("address",OFFSET(B3,COUNTA(B3:B10)-1,0))

copied to F1, J1

In D2 in Database , copied down

=ABS(AGGREGATE(15,6,Lists!$C$3:$K$7/(Lists!$B$3:$J$7=$B3),1)-AGGREGATE(15,6,Lists!$C$3:$K$7/(Lists!$B$3:$J$7=$C3),1))

For DV

Select B3:C5
Formula

=INDIRECT(INDEX(Lists!$B$1:$J$1,MATCH($A3,Lists!$A$3:$I$3,0)))

Whose option did you choose?

You can set up a range somewhere with all the routes in it and use that for your DV selection list.

File | Account - this is where you'll find details of your version in Excel.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Pl see file. I have created list manually for DV of Route.