Initial default drop downs and dependent default drop downs

Originally Posted by mehmetcik

Right Click on your sheet name at the bottom of excel and select view code.

Paste this code in the window that opens and close the window.

Your data validation for types needs amending too

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub

Set Z = Sheets("data").Rows(1).Find(Target.Value, LookIn:=xlValues, Lookat:=xlWhole)

T = Range(Cells(2, Z.Column), Cells(5, Z.Column)).Address

Range("B3:B6").Value = Sheets("data").Range(T).Value

End Sub

Hi there!

Thank you for being so helpful! I have a workbook I am working on and it's a doozy.

I just noticed this doesn't show a selectable drop down list for the items if you make a type that has spaces in it. I renamed fruits to juicy fruits. I tried putting in [code] =INDIRECT(SUBSTITUTE(B1," ",""))[code] for the validation source and it validated to an error and still didn't work.

Forget the calling the items out of order for a second. If I were to use this code and just put the data in the order I would like it to appear, add more items, and want a blank row between the next items on the Drop down sheet, what would I change to do that? I've added the data and tried to add the B8:B9 and B11 ranges [CODE] Range("B3:B6,B8:B9,B11").Value [CODE], but, as you can see, that didn't work.