!!!!! Tricky ----> Excel Dropdown menu function (super tricky!!!!)

[cosmicsport]

Hi experts, I have a sheets that contains drop down menus in every cell in column K that contain #s 1-8 . If I choose a number in the drop down menu I would like the flowing dd menus deactivate or disappear. Let's say I chose 3 in dd menu in K1 the dd menus in K2 and K3 are getting deactivated or disappear.

Any ideas ? If possible without VBA Macro

 

[tusharm]

Sure, it's possible. And, it's fairly straightforward. {grin}

Suppose you have the list of acceptable values in M1:M8. Then, in N1 enter the text 'Selection already made' (w/o the quotes).

Now, create a named formula (Formulas tab | Defined Names group | Name Manager button).

PickList =IF(COUNTA(Sheet2!$K$1:$K$3)=0,Sheet2!$M$1:$M$8,Sheet2!$N$1)

Next, select K1:K3 then Data tab | Data Tools group | Data Validation drop down > Data Validation...

In the resulting dialog box, in the Settings tab, In Allow select List. In Source enter =PickList.

That's it. Select from the drop down in any of K1:K3 and the data validation choices in all the cells will become 'Selection already made'.

Hi experts, I have a sheets that contains drop down menus in every cell in column K that contain #s 1-8 . If I choose a number in the drop down menu I would like the flowing dd menus deactivate or disappear. Let's say I chose 3 in dd menu in K1 the dd menus in K2 and K3 are getting deactivated or disappear.

Any ideas ? If possible without VBA Macro

 

[cosmicsport]

That works great. What if I have 200 rows and I need it work all independently ?

 

[tusharm]

What does that mean? Particularly, "work all independently"

That works great. What if I have 200 rows and I need it work all independently ?

 

[cosmicsport]

i have 200 rows and in column K are drop down menus in every cell.
They dd menus contain either 1 or 2 to chose from. If i chose 1 the next dd menu is available to chose from If I chose 2 the next following dd menu is blocked(and so on)
Since the code in the formula has a fixed range in it I cannot just copy it down because dd menu in row 150 for example will always reference to K1-K3 in the formula you showed me.

 

[cosmicsport]

the correct formula would be for drop down menu in K7:
=IF('Argen Euro'!$K11=2,Controls!$R$5,IF(COUNT('Argen Euro'!$K$2:$K12)=0,Controls!$R$5,IF($K13<>"",Controls!$R$5,Controls!$Q$6:$Q$8)))


for K178 it would be :
=IF('Argen Euro'!$K177=2,Controls!$R$5,IF(COUNT('Argen Euro'!$K$2:$K178)=0,Controls!$R$5,IF($K179<>"",Controls!$R$5,Controls!$Q$6:$Q$8)))

but i don't want to create 200 defined names and assign them to the drop down menus. Any ideas ?

 

[tusharm]

I am sure you know what you want but the formulas you've shared
(1) seem to have nothing to do with the original request,
(2) are inconsistent in the cells the reference -- the 1st one (in K7) starts with reference to a cell 4 rows below it (K11) while the 2nd (in K178)starts with a reference to a cell 1 row above it (K177),
(3) both include reference to the cell containing the formula, which will result in a circular reference.

the correct formula would be for drop down menu in K7:
=IF('Argen Euro'!$K11=2,Controls!$R$5,IF(COUNT('Argen Euro'!$K$2:$K12)=0,Controls!$R$5,IF($K13<>"",Controls!$R$5,Controls!$Q$6:$Q$8)))


for K178 it would be :
=IF('Argen Euro'!$K177=2,Controls!$R$5,IF(COUNT('Argen Euro'!$K$2:$K178)=0,Controls!$R$5,IF($K179<>"",Controls!$R$5,Controls!$Q$6:$Q$8)))

but i don't want to create 200 defined names and assign them to the drop down menus. Any ideas ?

 

[cosmicsport]

Thanks for your help ! This is what I did :
Instead of defining a formula name, I copied the entire formula into the data validation but placed all references cells on the same sheet.
Than I am hiding the columns. I can easily copy all dropdown menus down ad the references will change with it.

=IF(K2="",$U$3:$U$5,IF(K3="",$U$3:$U$4,V2))

 

[tusharm]

Glad you got that straightened out.

Thanks for your help ! This is what I did :
Instead of defining a formula name, I copied the entire formula into the data validation but placed all references cells on the same sheet.
Than I am hiding the columns. I can easily copy all dropdown menus down ad the references will change with it.

=IF(K2="",$U$3:$U$5,IF(K3="",$U$3:$U$4,V2))

 

[cosmicsport]

Ok one more this for everybody how is running into the same problem.
I found out that the data validation has a character limit for the formula entry. I had to create 199 defined Names and enter assign them each to the Drop down menu under data validation. So I wrote this macro that gets the formulas separate cells, creates defined name ranges and assigns them each to the drop down menus !

Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
End Function
Sub make_names()


Dim Krs As String
'Dim Rng As String
Dim nm As String

For i = 3 To 199
nm = "Ranger_"
Krs = GetFormula(Cells(i, 5))

'''Rng = Krs
ActiveWorkbook.Names.Add Name:=nm & i, RefersTo:=Krs

''enter into dat validation
Cells(i, 11).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & _
nm & i
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Next i
End Sub

I hope it helps