Drop down list to populate next drop down list and then auto add row after selection

[MrSTruct]

Hi all,

I want to create a sheet where there will be a drop down list on first column and a second connected to first one on the next column. Once I select a value on the first one, I want the next column to bring up the relevant drop down list from predetermined values ( essentially the first column will be the title of the table from pick list data validation and the next one the value I want to pick.

What I want next is after I pick one value from the 2nd drop down , if possible excel to add another row underneath and allow for additional selections, ideally with the first one appearing as a merged big one with only the title inside (doesn't have to be merged just look like one, also if that is not possible that is not extremely important). I think I have seen that somewhere but can't find it. Bonus : If I leave last (2nd column ) selection unpicked and choose some new title on left (eg vegetable) can it just undo addition of new row?- and have it appear like vegetable 2 items then fish (with no (select vegetable between). If not we can manually delete row.

Hope I explained this clearly. Is this doable within excel?

Please point me to relevant thread if there is something somewhere which I have missed

fruit	apple
	orange
	(select fruit)
vegetable	pepper
	lettuce
fish	cod

Thanks in advance.
T

 

[Yongle]

Before I make detailed suggestions I need to understand how the data in the sheet is being used and what else is around it

Can you explain what else is going on in the worksheet
- is there anything above or below the data?
- are there other dependent columns to the right of the above data? (quantities etc)

thanks

 

[MrSTruct]

Before I make detailed suggestions I need to understand how the data in the sheet is being used and what else is around it

Can you explain what else is going on in the worksheet
- is there anything above or below the data?
- are there other dependent columns to the right of the above data? (quantities etc)

thanks

Thanks for your reply, this is indeed only a part of the sheet, there are a lot of things going on above it and next columns, although they are all completely independent from it. The said section i inquired about is only picking data from another sheet in the workbook where all the picklists-tables are. I think most of it could be done by only introducing a list and then indirect or something like that. But I also want this additional row to add automatically, so that users don't have to manually copy and insert lines in order to pick 10 or more inputs from one category. I will try to send a snapshot of the form a bit later if it would help.
thanks

 

[Yongle]

Are you using named ranges for the dropdown lists in column B
- if so what are they called?

 

[MrSTruct]

Yes, that is the plan. In the example above, the Tables would be like : Table 1 header : Fruit , items : apple, orange, apricot, etc , Table 2 header Vegetable, items: pepper, lettuce, .. etc

 

[Yongle]

The code below is triggered when value is selected from dropdown in any cell in column B
- new cells inserted in column A & B
- the code only works if placed in sheet code window
- workbook must be saved as macro enabled

Testing
- insert the code as instructed
- select value from any dropdown in any cell in column B

Feedback
- code should achieve most of what you want
- it probably needs tweaking to behave exactly as required
- if you need further help let us know

Right-click on sheet name tab \ select View Code and paste code into the open window

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim validate As Boolean
    With Target
        If .CountLarge > 1 Or .Column <> 2 Then Exit Sub
        On Error Resume Next
            validate = (.Validation.Type = xlValidateList)
        On Error GoTo 0
        If validate Then
            Application.EnableEvents = False
                .Resize(1, 2).Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                .Offset(1).Activate
            Application.EnableEvents = True
        End If
    End With
End Sub

 

[MrSTruct]

I tried in my original sheet, I also created a new one with just the 2 columns, saved as macro-enabled, made sure all is correct in name manager and nothing happens. I will try to extensively look into it on the weekend, meanwhile if you could break it down a bit to me, it would be great!

In any case I'll let you know. thanks

 

[Yongle]

Hope this helps
Code amended slightly and comments added to clarify what happens in every line
You must get this working in a new workbook BEFORE trying it in your original workbook


Step 1
Create a NEW workbook
Paste this macro in Sheet1 code window
Click on a different cell and the message box tells you which cell you clicked on
If the message box does not appear then the code is in the wrong place
Right-click on sheet1 name tab \ select View Code \ paste code into the open window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "Triggered by selecting " & Target.Address(0, 0)
End Sub

Step 2
After confirming code is in the correct place ...
- paste this macro into the same code window
- delete the macro in Step1

Private Sub Worksheet_Change(ByVal Target As Range)
'declare a boolean variable whose value is TRUE or FALSE
    Dim validate As Boolean
'only trigger action if there is one cell being amended and if that cell is in column B
    If Target.CountLarge > 1 Or Target.Column <> 2 Then Exit Sub
'prevnt code stopping if error is encountered
    On Error Resume Next
'value held in variable validate is TRUE if Target has Data validation using List
    validate = (Target.Validation.Type = xlValidateList)
'reset error handling
    On Error GoTo 0
    If validate = True Then
'prevent new events being triggered when this macro changes values in cells
        Application.EnableEvents = False
'insert cells in A & B
        Target.Resize(1, 2).Offset(1, -1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'select inserted cell in column B
        Target.Offset(1).Activate
're-enable events trigger
        Application.EnableEvents = True
    End If
End Sub

Step3
Create Data Validation as below for B2

Step4
Select a value from the list in B2
- cursor should go to B3 which now also has data validation


Feedback
Hopefully that will get you started and then we can amend it to make it work as required

 

[MrSTruct]

Hi, sorry for my delayed response again, this does work. I played around a bit with indirect and a main list for first column, then a 2nd column. I will try to fit it in a test workbook with all the rest of my data in there and other formulas and get back to you! Again thanks! It's a pretty big task what I am trying to create here which has many complexities in it but this is a good first step for this part. I will let you know how it goes within the next days!

Thanks