Conditional Drop Down List

drubin25

Board Regular
Joined
Mar 19, 2016
Messages
62
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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
199
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
54,348
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

Board Regular
Joined
Mar 19, 2016
Messages
62
@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
199
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

try it and see
 

drubin25

Board Regular
Joined
Mar 19, 2016
Messages
62
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
54,348
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,012
Messages
5,834,923
Members
430,326
Latest member
tomwax46

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