+ Reply to Thread
Results 1 to 16 of 16

Dynamic Drop Down Lists

  1. #1
    Registered User
    Join Date
    09-30-2018
    Location
    Lancashire, England
    MS-Off Ver
    365
    Posts
    17

    Question Dynamic Drop Down Lists

    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:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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 and is accessed by a lookup table:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I have tried simply substituting the fixed sheet name for the above as follows:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    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.

    Thanks in advance

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,773

    Re: Dynamic Drop Down Lists

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    09-30-2018
    Location
    Lancashire, England
    MS-Off Ver
    365
    Posts
    17

    Re: Dynamic Drop Down Lists

    Thanks

    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've attached the example file. (I hope)

    Any workable solution is welcomed, Thanks again
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-30-2018
    Location
    Lancashire, England
    MS-Off Ver
    365
    Posts
    17

    Re: Dynamic Drop Down Lists

    Working through the formula, 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
    Screenshot (189).png
    Screenshot (190).png
    Screenshot (191).png
    Screenshot (192).png
    Screenshot (193).png

  5. #5
    Registered User
    Join Date
    09-30-2018
    Location
    Lancashire, England
    MS-Off Ver
    365
    Posts
    17

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,311

    Re: Dynamic Drop Down Lists

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    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.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    09-30-2018
    Location
    Lancashire, England
    MS-Off Ver
    365
    Posts
    17

    Re: Dynamic Drop Down Lists

    you are all an absolute joke

    nobody is here to actually help or encourage anyone

    i posted this BEFORE posting anywhere else, and linked that post back to here (and considering you also admin that site) its ridiculous you then post that nonsense about rules.

    i have even assisted someone on the other forum, and yet zero help from any so called admin! Clearly none of you can actually do anything to help anyone, just criticise and point out ludicrous rules


    ...and of course no doubt now remove this post and ban me from your forums!

    What a joke

    https://www.excelguru.ca/forums/show...-using-formula
    Last edited by mAdMaCCa; 01-22-2020 at 08:34 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,311

    Re: Dynamic Drop Down Lists

    Less of the invective, please!

    The rule is the same, regardless of the order in which you posted the queries. Thank you for providing the link.

    I have no desire to ban you from this forum.

    zero help from any so called admin!
    People offer help when they can help with your query. Being an admin (mod, actually, in my case) does not mean I can answer all queries!
    Last edited by AliGW; 01-22-2020 at 08:36 AM.

  9. #9
    Registered User
    Join Date
    09-30-2018
    Location
    Lancashire, England
    MS-Off Ver
    365
    Posts
    17

    Re: Dynamic Drop Down Lists

    I am sure the real reason the rule is there is to make your pages appear more relevant to search engines and push them up the rankings, but fair play, I totally get that and it makes good business sense in terms of advertising, just think it is unrealistic to expect people to jump back and forth posting links everywhere when nobody seems to actually post any solutions.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,311

    Re: Dynamic Drop Down Lists

    The vast majority of threads here DO get solutions - just have a look. I post a fair few myself! Usually, when someone does not get the help they need, it is because their query isn't clear. I would look at this myself if I weren't at work and just about to go into a meeting.

    Your assumption about the reason for the rule is entirely wrong, by the way, and if you follow the red link in post #6, you'll be able to read the reasoning that all forums share on this.

    I hope someone will be available to jump in and help you today. Good luck.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,773

    Re: Dynamic Drop Down Lists

    I downloaded your file from Post #3 yesterday to take a look, but there was little in it by way of explanation, and it seemed to contradict the screenshots that you show in Post #4 (which refer to evaluation of a formula in Sheet1!F8 but there is nothing in that cell in your file).

    The file shows 3 boxed cells on row 3 of Sheet1, and I presume these are where you want to have drop-downs, but none of these have a drop-down set up.

    In Post #3 you refer to lists having blanks in them, but the data seems to be contiguous in all the subsidiary sheets, so I don't know what lists you are referring to.

    It might be better to describe what you want to achieve overall, rather than give us some formulae which you say do not work.

    It would seem to me that in the 3rd formula that you quote in Post #1 would need to have an INDIRECT function within the COUNTA function, but then as INDIRECT is a volatile function you will not be allowed to have an INDIRECT within another INDIRECT, so another approach would be needed, but that can only emerge once we have a clearer understanding of what you are trying to achieve (e.g. even if we manage to get these dynamic dependent drop-downs working, what do you want to do with them?).

    It might be that a different data layout would help matters - i.e. instead of having 4 lists in different sheets, it might be better to have these lists on one sheet (so you wouldn't need to use INDIRECT to select the sheet that contains the appropriate list).

    My time is valuable (to me, at least), and it is given for free on these forums to help others, but I don't agonise over a post if it doesn't make sense to me - I just move on. Other potential helpers may have made the same decision, so if you want some help then you should do what you can to help others understand your problem, rather than criticise a Moderator (and the Forum as a whole) for not jumping up to answer your query.

    Pete

  12. #12
    Registered User
    Join Date
    09-30-2018
    Location
    Lancashire, England
    MS-Off Ver
    365
    Posts
    17

    Question Re: Dynamic Drop Down Lists

    Quote Originally Posted by Pete_UK View Post
    ...

    My time is valuable (to me, at least), and it is given for free on these forums to help others, but I don't agonise over a post if it doesn't make sense to me - I just move on. Other potential helpers may have made the same decision, so if you want some help then you should do what you can to help others understand your problem, rather than criticise a Moderator (and the Forum as a whole) for not jumping up to answer your query.

    Pete

    Firstly, let me thank you for taking the time to look at the file and problem, it is genuinely appreciated. I know peoples time is valuable, I know because so is mine and I appreciate anyone giving up theirs to assist someone else.

    However, I did not criticise the Moderator for not jumping up and answering my query, but for (and not this mod specifically) the countless post that seem to simply point out to people in need of assistance that they have done something wrong and to create link after link pointing here there and back again. Totally demoralising and frustrating when someone is struggling with a problem. However, I do apologise for any offence caused.

    The file I created and attached is a small (very much simplified) snippet of a much larger workbook with around 60 sheets, many of which reference each other for numerous reasons, so unfortunately putting everything on sheet is not an option for me. The snippet was to demonstrate, with real but reduced data what I have and what I am trying to achieve.

    As I mentioned in my original post, I did find it difficult to translate to words my objectives so I again apologise if it has been unclear.

    I totally get the confusion from the evaluation screenshots, but the actual source formula for the drop down lists is of course in Data Validation, so I cannot "evaluate" that, so instead I copied the formula temporarily in to cell F8 literally to allow me to run the evaluation and take screenshots of what was happening during the execution of the formula as a data source for the drop down. It would never produce the outcome I need in cell F8 because that is not defined as a list, so again, my apologies for the confusion.

    No idea if this will help, but attached is a further/supplemental explanation file that I am hoping will.

    EDITED because I should also have explained that the "Developments" start and finish over a period of time, which is the other reason they are on separate sheets. For on going maintenance it is easier for the whole workbook to simply add a development with a new sheet name or remove one, leaving the rest of the workbook unchanged but working.
    Attached Files Attached Files
    Last edited by mAdMaCCa; 01-22-2020 at 12:37 PM.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,773

    Re: Dynamic Drop Down Lists

    I'm just about to go out, but I'll take a look later on when I get back in.

    Pete

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,686

    Re: Dynamic Drop Down Lists

    This proposal places the following in cell C1 of each development abbreviation sheet: =COUNTA(A3:A40)
    This was quickly done by selecting the hkr sheet tab, then pressing the shift key and selecting the spp sheet tab. The above formula was then typed into cell C1 of the hkr sheet only. Excel placed the formula in cell C1 on all sheets from hkr through spp.
    The data validation of cell F3 is then modified to read: =INDIRECT(VLOOKUP(B3,Developments!A2:B9,2,FALSE) & "!$A$3:$A$" & INDIRECT(VLOOKUP(B3,Developments!A2:B9,2,FALSE)&"!C1")+2)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  15. #15
    Registered User
    Join Date
    09-30-2018
    Location
    Lancashire, England
    MS-Off Ver
    365
    Posts
    17

    Re: Dynamic Drop Down Lists

    Simple yet brilliant solution.

    Many thanks, very much appreciated

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,686

    Re: Dynamic Drop Down Lists

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. Dependent Drop-Down w/ Dynamic Lists
    By covanpatten2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2016, 02:17 PM
  2. [SOLVED] Help with Dynamic Validation Lists / Drop Downs / dynamic filters
    By rjnc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2016, 08:10 PM
  3. Drop down box using data from dynamic lists
    By guerrilla_gorrila in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-06-2014, 08:25 PM
  4. Dynamic Drop Down lists
    By Excellearnerva in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-25-2014, 10:20 AM
  5. [SOLVED] Dynamic Ranges as drop down lists based on another drop down list
    By Excel_Beginner_1 in forum Excel General
    Replies: 4
    Last Post: 05-15-2012, 03:31 PM
  6. Dependent and dynamic drop-down lists
    By csunseri in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-24-2011, 04:32 AM
  7. Dynamic suggestion drop down lists
    By wesmortimer in forum Excel General
    Replies: 3
    Last Post: 10-27-2010, 12:25 PM

Tags for this Thread

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