+ Reply to Thread
Results 1 to 8 of 8

Drop Downs and associated Formula

  1. #1
    Registered User
    Join Date
    08-06-2021
    Location
    Ireland
    MS-Off Ver
    2105
    Posts
    4

    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
    Attached Files Attached Files
    Last edited by Gortnalea; 08-06-2021 at 05:31 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,545

    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.
    Attached Files Attached Files
    Last edited by AliGW; 08-07-2021 at 03:13 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,090

    Re: Drop Downs and associated Formula

    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)))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    08-06-2021
    Location
    Ireland
    MS-Off Ver
    2105
    Posts
    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. #5
    Registered User
    Join Date
    08-06-2021
    Location
    Ireland
    MS-Off Ver
    2105
    Posts
    4

    Re: Drop Downs and associated Formula

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

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,545

    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.

    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.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,090

    Re: Drop Downs and associated Formula

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

  8. #8
    Registered User
    Join Date
    08-06-2021
    Location
    Ireland
    MS-Off Ver
    2105
    Posts
    4

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Expenses formula (drop-downs and IF-formulas) / Noobie
    By Likane in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2020, 03:14 AM
  2. [SOLVED] Formula for multiple drop-downs linked to each other
    By luajambeiro in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-12-2019, 10:40 AM
  3. Initial default drop downs and dependent default drop downs
    By hudsone777 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-07-2016, 07:05 PM
  4. [SOLVED] Drop Downs conditional on drop downs
    By BillTD in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-06-2015, 09:40 AM
  5. Formula with drop downs
    By DatGuy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2013, 01:51 PM
  6. Count Formula With Drop Downs
    By Zoe Lees in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-05-2012, 11:51 AM
  7. Use Drop Downs to Populate Other Drop Downs
    By fbombs in forum Excel General
    Replies: 2
    Last Post: 09-23-2009, 03:17 PM

Bookmarks

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