# Drop Downs and associated Formula

1. ## Drop Downs and associated Formula

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

2. ## Re: Drop Downs and associated Formula

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.

3. ## Re: Drop Downs and associated Formula

In B1 of lists sheet

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)))

4. ## Re: Drop Downs and associated Formula

AliGW & kvsrinivasamurthy,

Thank you both so much. I really appreciate it. I have been scratching my head and googling formula's etc but to no avail. Why didn't I come to you genius' earlier - would have saved me alot of time!!

Just one other issue I have; I need to be able select the routes too from a drop down in column A. Is this possible?

Its great to have this forum to learn from you hopefully i'll be able to help others then too.

5. ## Re: Drop Downs and associated Formula

Also, how do I find which version of excel I have?

6. ## Re: Drop Downs and associated Formula

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.

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.

7. ## Re: Drop Downs and associated Formula

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

8. ## Re: Drop Downs and associated Formula

Thank you so much for all the help and tips AliGW.

I haven't chosen the one I will go with yet. I want to have a look at them more closely and see which formulas make it easier for me to understand for again.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1