reducing drop down menu after every selection

[EMcK01]

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-ge...-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]

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]

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]

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

Excel 2012
	A	B	C
1	Bert	1	Bert
2	Fred	2	Fred
3	Joe	3	Joe
4	Mike	4	Mike
5	Pete	5	Pete
6	Sam	6	Sam
Sheet4

Cell Formulas
Range	Formula
B1	=IF(COUNTIF(Sheet3!$B$2:$G$2,A1)>=1,"",ROW())
B2	=IF(COUNTIF(Sheet3!$B$2:$G$2,A2)>=1,"",ROW())
B3	=IF(COUNTIF(Sheet3!$B$2:$G$2,A3)>=1,"",ROW())
B4	=IF(COUNTIF(Sheet3!$B$2:$G$2,A4)>=1,"",ROW())
B5	=IF(COUNTIF(Sheet3!$B$2:$G$2,A5)>=1,"",ROW())
B6	=IF(COUNTIF(Sheet3!$B$2:$G$2,A6)>=1,"",ROW())
C1	=IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$6),"",INDEX(A:A,SMALL(B$1:B$6,1+ROW(A1)-ROW(A$1))))
C2	=IF(ROW(A2)-ROW(A$1)+1>COUNT(B$1:B$6),"",INDEX(A:A,SMALL(B$1:B$6,1+ROW(A2)-ROW(A$1))))
C3	=IF(ROW(A3)-ROW(A$1)+1>COUNT(B$1:B$6),"",INDEX(A:A,SMALL(B$1:B$6,1+ROW(A3)-ROW(A$1))))
C4	=IF(ROW(A4)-ROW(A$1)+1>COUNT(B$1:B$6),"",INDEX(A:A,SMALL(B$1:B$6,1+ROW(A4)-ROW(A$1))))
C5	=IF(ROW(A5)-ROW(A$1)+1>COUNT(B$1:B$6),"",INDEX(A:A,SMALL(B$1:B$6,1+ROW(A5)-ROW(A$1))))
C6	=IF(ROW(A6)-ROW(A$1)+1>COUNT(B$1:B$6),"",INDEX(A:A,SMALL(B$1:B$6,1+ROW(A6)-ROW(A$1))))

 

[L. Howard]

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