EXCEL 2013 DEPENDENT CELLS

[lcappsupport]

I am using Excel 2013 and I want to have a list of items in one cell and depending on which item is selected, I want to have a second list of items shown that are dependent upon the first selection.

For example, in column A I have a data validation look up to a list of items. In column B, I want to show related items dependent upon what is selected in column A. If a user selects food, I want to have column B only show food items. However, if the user selects cars from column A, I want column B to only show car options.

I have a total of 99 various items in column A that could be selected.

[pike]

Hello Icappsupport,
Welcome to the forum
This is a good step up step example which will help to get you started. Little bit of work involved to set up the named ranges ect.
http://www.contextures.com/xlDataVal02.html

[werafa]

I agree that named ranges are the way to go. I'm building something very similar as I speak.
you might wish to consider Dynamic Named Ranges - these will automatically resize to fit whatever data is entered in a particular range.
This is handy when you need to change a list either manually or programatically. It means that you can select a list, have this list populate a particular (named) range, and the named range will automatically adjust to the new list's size without further programming.

Werafa

[lcappsupport]

So does this mean for each of the 99 different entries I have in the column A I will need to create a separate list somewhere else on the spreadsheet for each item a user selects from Column A to show a result in Column B?

[p45cal]

So does this mean for each of the 99 different entries I have in the column A I will need to create a separate list somewhere else on the spreadsheet for each item a user selects from Column A to show a result in Column B?

No.
Look at the link in msg#2

[werafa]

p45cal is correct.

in the case of data validation, the validation list is applied to the cell, and the entered value does not change unless there is a user input.
The way that I did it is to turn the range values into a list, and then enter this list into the validation settings - then create a new list of values to match the new user selection, and enter this list into the validation on the new row. All you need is sufficient info to call or create the list you need - as and when you need it. (I'm assuming that you have a database with (for example) a list of product categories and a subsequent list of products, both of which are implemented as data validation selection menus)

there is one thing to watch out for: there is a bug in excel that affects this functionality - if you get a corrupted workbook when opening the file, change the way you implement the validation list. There is (in my experience) no loss of data or functionality in the workbook if the bug does occur, but it is not a good look.

[werafa]

Here is the code I use.
key point is, the validation is entered as a string, not a range. (this avoids the excel bug). this sets a value (the auto cost centre), and then sets the validation to permit an override from preset values.

Sub ManualCostCentre(myRange As Range, mySheet As Worksheet)' run the auto - cost centre routine, then permit a manual override
Dim myCol As Long
Dim myString As String


    Call AutoCostCentre(myRange, mySheet)
    
    'prep validation list
    myCol = 3
    Do While mySheet.Cells(2, myCol + 1).Value <> ""
        myCol = myCol + 1
    Loop
    myString = GetOverrideCCList
    
    With myRange.Offset(, 2).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=myString
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = False
        .ShowError = False
    End With
    
End Sub


Function GetOverrideCCList() As String
Dim mySheet As Worksheet
Dim myRow As Long
Dim myString As String


Set mySheet = ThisWorkbook.Worksheets("PM List")
myRow = 4


    myString = mySheet.Range("J3").Value
    Do While mySheet.Cells(myRow, 10).Value <> ""
        myString = myString & "," & mySheet.Cells(myRow, 10).Value
        myRow = myRow + 1
    Loop
    
GetOverrideCCList = myString
End Function