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

MrSTruct

New Member
Joined
Jul 10, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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)
vegetablepepper
lettuce
fishcod

Thanks in advance.
T
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 10, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
Are you using named ranges for the dropdown lists in column B
- if so what are they called?
 

MrSTruct

New Member
Joined
Jul 10, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

New Member
Joined
Jul 10, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,872
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
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
Data validation B2.jpg


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
 
Last edited:

MrSTruct

New Member
Joined
Jul 10, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,800
Messages
5,574,402
Members
412,590
Latest member
Velly
Top