reducing drop down menu after every selection

EMcK01

Board Regular
Joined
Jun 14, 2015
Messages
87
Hi,

Looking for some help. I would like to create a reducing drop down menu for a range of cells in a column when a value is selected. The example at the link here is essentially what I am looking for: https://www.excelforum.com/excel-general/788402-excel-2007-how-to-create-drop-down-list-but-options-reducing-after-every-seclection.html

However I would like to have this over a series on columns. So based on the example that is shown in the link, rather than it applying to column C, I would like this to apply to several columns, but all based on the same range of values in the list.

Looking at the code within the Sheet, it appears that I need to extend the ranges. Rather than applying to column C only, how do I update it to include all columns in my range?

I'm not sure what the protocol is for publishing someone else's script, however happy to update this post if its ok and makes it easier to respond to my query.

Any help would be appreciated.

Thanks,
EMcK
 

KJefferies

New Member
Joined
Oct 20, 2017
Messages
4
Hi EMck

Not sure if this will help.

I think i had a similar requirement and got round it using a reducing dropdown list and Vlookups. I have mocked up some data below to show this.

Copy the list of choices into cells B4 - B6 and the details into cells C4 - C6
Film
Starwars
TV
Cartoon


GamesConsole
Xbox




<tbody>
</tbody>

"In List" {cells D4-D6} Column used Check to see if used (Formula entered and copied down)
=IF(COUNTIF($G$4:$G$6,B4)>=1,TRUE,FALSE)
"Dropdown" {cells E4-E6} is an Array formula that checks the Inlist values and only shows the ones that are not used (entered using Ctrl + alt + Enter and them copied down)
=IFERROR(INDEX($B$4:$B$6,MATCH(0,COUNTIF($E$3:E3,$B$4:$B$6)+IF($D$4:$D$6=TRUE,1,0),0)),"")
Set Data Validation {Cells G4-G6 } used to control the drop down is a Dynamic Range
=OFFSET(E:E,3,,COUNTIF(E:E,"?*")-1)
add Vlookup {cells H4-H6}
=IFERROR(VLOOKUP(G4,$B$4:$C$6,2,FALSE),"")

<tbody>
</tbody>

Regards

Keith
 

EMcK01

Board Regular
Joined
Jun 14, 2015
Messages
87
Keith,

thanks for the response, not sure I understand exactly what you are trying to show me.

Looking at the formula's it appears to do a lot of work in adjacent columns where the range where the drop down list is, however I want the same drop down to apply to other columns rather than just column C as it is set up just now. So any additional works going on in adjacent columns won't be practical for me.

thanks anyway.
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
If you can not have "...any additional works going on in adjacent columns..." how about another sheet...?? if so then give this a try.

I will use sheets3 and sheet4 for my example.

On sheet3 there are six drop downs in row two, cells B2 to G2. All six Source boxes of those drop downs refer to this range on sheet4: =Sheet4!$C$1:$C$6

As you select a name in any of the drop downs on sheet3, that name is excluded from the drop down Source range on sheet4, therefor only usable once.
If all names are used, you can reset the source range by selecting the six drop downs and tapping DELETE. You have a new full list again on sheet4 column C.Howard

Sheet4 is set up as such...

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Bert</td><td style="text-align: center;;">1</td><td style="text-align: center;;">Bert</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Fred</td><td style="text-align: center;;">2</td><td style="text-align: center;;">Fred</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Joe</td><td style="text-align: center;;">3</td><td style="text-align: center;;">Joe</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Mike</td><td style="text-align: center;;">4</td><td style="text-align: center;;">Mike</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Pete</td><td style="text-align: center;;">5</td><td style="text-align: center;;">Pete</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Sam</td><td style="text-align: center;;">6</td><td style="text-align: center;;">Sam</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">Sheet3!$B$2:$G$2,A1</font>)>=1,"",ROW(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=IF(<font color="Blue">ROW(<font color="Red">A1</font>)-ROW(<font color="Red">A$1</font>)+1>COUNT(<font color="Red">B$1:B$6</font>),"",INDEX(<font color="Red">A:A,SMALL(<font color="Green">B$1:B$6,1+ROW(<font color="Purple">A1</font>)-ROW(<font color="Purple">A$1</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">Sheet3!$B$2:$G$2,A2</font>)>=1,"",ROW(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">ROW(<font color="Red">A2</font>)-ROW(<font color="Red">A$1</font>)+1>COUNT(<font color="Red">B$1:B$6</font>),"",INDEX(<font color="Red">A:A,SMALL(<font color="Green">B$1:B$6,1+ROW(<font color="Purple">A2</font>)-ROW(<font color="Purple">A$1</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">Sheet3!$B$2:$G$2,A3</font>)>=1,"",ROW(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">=IF(<font color="Blue">ROW(<font color="Red">A3</font>)-ROW(<font color="Red">A$1</font>)+1>COUNT(<font color="Red">B$1:B$6</font>),"",INDEX(<font color="Red">A:A,SMALL(<font color="Green">B$1:B$6,1+ROW(<font color="Purple">A3</font>)-ROW(<font color="Purple">A$1</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">Sheet3!$B$2:$G$2,A4</font>)>=1,"",ROW(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=IF(<font color="Blue">ROW(<font color="Red">A4</font>)-ROW(<font color="Red">A$1</font>)+1>COUNT(<font color="Red">B$1:B$6</font>),"",INDEX(<font color="Red">A:A,SMALL(<font color="Green">B$1:B$6,1+ROW(<font color="Purple">A4</font>)-ROW(<font color="Purple">A$1</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B5</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">Sheet3!$B$2:$G$2,A5</font>)>=1,"",ROW(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C5</th><td style="text-align:left">=IF(<font color="Blue">ROW(<font color="Red">A5</font>)-ROW(<font color="Red">A$1</font>)+1>COUNT(<font color="Red">B$1:B$6</font>),"",INDEX(<font color="Red">A:A,SMALL(<font color="Green">B$1:B$6,1+ROW(<font color="Purple">A5</font>)-ROW(<font color="Purple">A$1</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B6</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">Sheet3!$B$2:$G$2,A6</font>)>=1,"",ROW(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C6</th><td style="text-align:left">=IF(<font color="Blue">ROW(<font color="Red">A6</font>)-ROW(<font color="Red">A$1</font>)+1>COUNT(<font color="Red">B$1:B$6</font>),"",INDEX(<font color="Red">A:A,SMALL(<font color="Green">B$1:B$6,1+ROW(<font color="Purple">A6</font>)-ROW(<font color="Purple">A$1</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
The sheet 4 set up formulas looks a bit massive, actually you copy & paste the formulas for B1 and C1 into their cells and pull then down.

Howard
 

Forum statistics

Threads
1,082,250
Messages
5,364,022
Members
400,774
Latest member
Goldi paul

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top