Conditional Drop Down List

drubin25

New Member
Joined
Mar 19, 2016
Messages
22
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?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,391
Office Version
  1. 365
Platform
  1. Windows
@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

New Member
Joined
Mar 19, 2016
Messages
22
@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

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

try it and see
 

drubin25

New Member
Joined
Mar 19, 2016
Messages
22
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,391
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1CarsFruitVegetables
2FordCarrot
3HondaApplePumpkin
4Banana
5ToyotaPear
6
7
8Peach
9
10
11
12
13
14
15
16
17
18
19
20FordAppleCarrot
21HondaBananaPumpkin
22ToyotaPear
23Peach
24
Dropdowns
Cell Formulas
RangeFormula
B20:B22,D20:D21,C20:C23B20=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
FG
16Primary drop-downConditional Drop-down
17Fruit
Calculator
Cells with Data Validation
CellAllowCriteria
F17List=Dropdowns!$B$1:$D$1
G17:H17List=INDEX(Dropdowns!20:20,MATCH(F17,Dropdowns!1:1,0))#


1595821797401.png
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,215
Messages
5,623,433
Members
415,974
Latest member
ZorroOP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top