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
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: