Results 1 to 2 of 2

Thread: Dynamic Drop Down Lists using formula

  1. #1
    Seeker mAdMaCCa's Avatar
    Join Date
    Jan 2020
    Posts
    5
    Articles
    0
    Excel Version
    Home & Business 2016

    Question Dynamic Drop Down Lists using formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all


    I have lists of differing lengths across several sheets, so some may have 8 items, others up to 20.


    I have a formula that counts the items in "a" list to a maximum list length (the maximum is to allow for additional items to existing lists).



    The following formula works fine and produces a drop down list with no blank lines:
    =INDIRECT("spp" & "!$A$3:$A$" & COUNTA(spp!$A$3:$A30)+2) where spp is the sheet name


    However, to make the list dynamic and switch between different sheets (and therefore create different lists) I need to change the "spp" in the above example to the correct sheet name. To do this the user has a drop down list of sheet names that allows them to switch between sheets and therefore lists.


    The formula I use for different sheets is accessed by a lookup table:
    VLOOKUP(C9,Developments!A2:B9,2,FALSE)


    I have tried simply substituting the fixed sheet name for the above as follows:
    =INDIRECT(VLOOKUP(C9,Developments!A2:B9,2,FALSE) & "!$A$3:$A$" & COUNTA(VLOOKUP(C9,Developments!A2:B9,2,FALSE) & "!$A$3:$A$30")+2)


    But it seems to change the count to 1 and so only ever returns a list of one with the first item on a list.


    I appreciate I am not the best at explaining what I am after, but I am hoping it is clear enough that someone understand my issue and has an insight on how to fix it for me please.

    It may be that I am over-complicating my issue, but the whole issue is that on some of the lists I reference the blanks are removed, yet others are full of blank lines that means the user needs to scroll up to find the elements.

    I won't lie, I have posted this on another forum, however it would appear nobody there is up to the job so I am hoping I can find a solution here.

    Any workable solution is welcomed.

    Working through the formula using Evaluate, I think the problem is to do with Excel dumping quotes around part of the COUNTA results, so basically it is not counting, but I have no idea why or how to get round it.

    DynamicDropDownEG - Copy.xlsxEvaluateResults.pdf





    Thanks again


    Thanks in advance
    Last edited by mAdMaCCa; 2020-01-22 at 09:29 AM.

  2. #2
    Seeker mAdMaCCa's Avatar
    Join Date
    Jan 2020
    Posts
    5
    Articles
    0
    Excel Version
    Home & Business 2016
    Sorry, When I mentioned i had posted in another forum, I should have also posted a link to that forum just in case the issue gets resolved there. i appreciate there is a lot of crossover between forums and it is unusual to only be a member of one, so here it is...
    https://www.excelforum.com/office-36...own-lists.html

Posting Permissions

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