Dynamic Drop Down Lists using formula

mAdMaCCa

New member
Joined
Jan 22, 2020
Messages
5
Reaction score
0
Points
0
Excel Version(s)
Home & Business 2016
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.

View attachment DynamicDropDownEG - Copy.xlsxView attachment EvaluateResults.pdf





Thanks again


Thanks in advance
 
Last edited:
Back
Top