Dynamic Drop Down Lists

[mAdMaCCa]

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:

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:

Please Login or Register  to view this content.

I have tried simply substituting the fixed sheet name for the above as follows:

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

[Pete_UK]

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

[mAdMaCCa]

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

[mAdMaCCa]

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

[mAdMaCCa]

Attachment 658858
Attachment 658859
Attachment 658860
Attachment 658861
Attachment 658862

[AliGW]

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

[mAdMaCCa]

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

[AliGW]

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!

[mAdMaCCa]

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.

[AliGW]

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.

[Pete_UK]

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

[mAdMaCCa]

...

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.

[Pete_UK]

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

Pete

[JeteMc]

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.

[mAdMaCCa]

Simple yet brilliant solution.

Many thanks, very much appreciated

[JeteMc]

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.