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

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

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!

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.

Originally Posted by Pete_UK

...

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.

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.

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.