Conditional Drop Down List

[drubin25]

I have 2,500+ columns of cells that I have defined names so they can be used for conditional drop downs (i.e. if cell F10 = XXX, the drop down list in another cell only applies to what is in F10). Where I have listed my drop down options (the 2,500+ columns) some of them have multiple options and others have 1.

The problem I am trying to solve: Some of my drop down options have multiple blank lines.

Is there a way to not include blank lines without having to go through each column?

 

[Dermot]

If what you mean is that your dropdown options allow for (say) 5 options, but if (say) only 3 are needed, there will be two blanks as well, then, you can do this...

This works for Data Validation (in cell) dropdowns.

First of all, create the list of items you want included, somewhere on the sheet, say C5:C7 (ie 3 items)
In another cell, calculate the number of items you want shown, say D5 = 3

In the Data Validation setup, choose a list, then in the Source field, put this (using the example above)
=OFFSET(C5,0,0,D5,1)
This will only include the number of items you specified

 

[Peter_SSs]

@drubin25

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also be good to get a better feel for exactly the sort of thing you need if we could see a worksheet (preferably with XL2BB ) with a small sample of dummy data demonstrating what you have/need and an explanation in relation to that sample.

 

[drubin25]

@Dermot, I am already using =INDIRECT($F$10) in my source code for my data validation list. Am I able to combine this with your formula and still make it work?

If what you mean is that your dropdown options allow for (say) 5 options, but if (say) only 3 are needed, there will be two blanks as well, then, you can do this...

This works for Data Validation (in cell) dropdowns.

First of all, create the list of items you want included, somewhere on the sheet, say C5:C7 (ie 3 items)
In another cell, calculate the number of items you want shown, say D5 = 3

In the Data Validation setup, choose a list, then in the Source field, put this (using the example above)
=OFFSET(C5,0,0,D5,1)
This will only include the number of items you specified

 

[Dermot]

try it and see

 

[drubin25]

I had to change it up...

This worked: =OFFSET(Dropdowns!B1,1,MATCH(Calculator!F17,Dropdowns!$B$1:$CSR$1,0)-1,COUNTA(OFFSET(Dropdowns!B1,1,MATCH(Calculator!F17,Dropdowns!$B$1:$CSR$1,0)-1,18,1)),1)

 

[Peter_SSs]

Unfortunately we still do not know what Excel version you are using but IF you (or other readers with similar set-up) have Excel 365 with the FILTER function, another alternative that has simpler (non-volatile) formulas might be this.

We also haven't seen you exact setup but I am guessing that both the original drop-down & the conditional drop-down are on the 'Calculator' sheet. If not a small modification would be needed.

From looking at your formula in post 6 it appears that none of your lists on 'Dropdowns' are more than 18 items long & I have assumed that rows below that are available to use.
From your formula, it also appears that if there are blank rows in the lists on Dropdowns, those blanks are at the bottom of each list. Whilst that is tidy & a good way to go, the method I am proposing does not require that so if an item is later removed from the middle of one of the lists, there is no need to move the others up (though you may still choose to do that).


On Dropdowns row 20 I have placed the formula shown for B20. There is no need to copy this formula down as the other results will automatically 'spill' to any required rows. This B20 formula is then copied across the other columns with lists.

drubin25 2020-07-27 1.xlsm
	A	B	C	D
1		Cars	Fruit	Vegetables
2		Ford		Carrot
3		Honda	Apple	Pumpkin
4			Banana
5		Toyota	Pear
6
7
8			Peach
9
10
11
12
13
14
15
16
17
18
19
20		Ford	Apple	Carrot
21		Honda	Banana	Pumpkin
22		Toyota	Pear
23			Peach
24
Dropdowns

Cell Formulas
Range	Formula
B20:B22,D20:D21,C20:C23	B20	=FILTER(B2:B18,B2:B18<>"")
Dynamic array formulas.

The conditional Data Validation on Calculator becomes pretty simple and you will note there is no need for a count of items in any list. The DV is simply pointed at the top value in the relevant list & the DV 'knows' how long the list is. Anny additions/deletions in the main lists on Dropdowns will automatically flow through to the DV on Calculator.

drubin25 2020-07-27 1.xlsm
	F	G
16	Primary drop-down	Conditional Drop-down
17	Fruit
Calculator

Cells with Data Validation
Cell	Allow	Criteria
F17	List	=Dropdowns!$B$1:$D$1
G17:H17	List	=INDEX(Dropdowns!20:20,MATCH(F17,Dropdowns!1:1,0))#