data validationmicrosoft excel
After an extensive search, all I seem to find are cascading drop down list topics. My situation is:
If M173=true then allow drop down list in cell K72, otherwise blank.
For reference, M173 is true or false dependent on whether a check box is checked or not elsewhere on the sheet.
I've tried various if statements and vlookups inside cell K72 and even indirect after opening up the data validation tab.
Is this possible without VBA?
Using the method described in your original post How do i make a Data Validation drop-down exclude blanks.
Couple this with these methods:
Public Sub ClearDataValidation(destrng As Range)
destrng.Validation.Delete
End Sub
Public Sub LoadDataValidation(srcrng As Range, destrng As Range)
'Verify a 1x1 sized Range was passed
If destrng.Rows.Count <> 1 Or destrng.Columns.Count <> 1 Then
InvalidValue destrng.Worksheet, "LoadDataValidation", _
"Range: " & destrng.name & " was passed to method. This method expects a " & vbCrLf & vbCrLf & _
" 1 Row x 1 Column Range to be passed. Anything outside of the 1x1 " & vbCrLf & vbCrLf & _
"size will result in invalid conditions"
Exit Sub
End If
With Range(destrng.Address).Validation
.Delete
.Add xlValidateList, xlValidAlertStop, xlBetween, DistinctValues(srcrng)
End With
End Sub
LoadDataValidation Range("Table1[column1]"), Range("destinationCell")
Easy way in 4 simple steps
Step 1 Create your tables:
Step 2
Name your tables: Select the tables and rename them, so that it matches the value in the primary table Table1, eg. Table2 with the header "Cakes" should be named Cake, since that's the value in the primary table Table1.
But how? Click the corner of the table to select it, enter a new label where it says Table2 or similar and press enter. Don't worry if it still says "Table2" etc.
(Note! This is not necessary for the primary table eg. Table1.)
Step 3
Select where you want your first list, go to the data tab and into data validation.
Select "list" and enter: =INDIRECT("Table1") in the source window:
Step 4
Select a cell for the second list, that will be dependent on the first list.
Return to data validation, choose "list" and enter =INDIRECT(K2) where "K2" is the location of the first list.
DONE
For a third list, dependent on the second list, repeat step 4 and refer to the second list instead. Good luck!
Best Answer
Like you said you can use another cell M1 for example :
=If(M173=True,"Fine","No")I used Fine just in case you need Yes in another place.
Select the options of your Dropdown List and define Name
FineIn K72 create
Data Validation, chooseListIn Source write:
=Indirect(M1)In that case when M1 is different from
Finethe dropdown is empty, when you have Fine in M1 K72 will show the values that you want to choose from the listUpdate for Drop Down List become blank
Define name a blank cell
Nomake it in a special place so you don't use it for any value (near the Fine list or yes list)Keep the data validation Source like before =Indirect(M1)
If M1 = "No" the List will become blank (you have to choose the blank to clear the cell if it has any value)
If M1="Yes" or "Fine" whatever you choose the List will have the options you need
Just use normal word for Define Name do not use
TRUE,False...To Clear it automatically you will need VBA