How to create dynamic list based on value of another list

G

GuyGadois

Active Member
Joined
Jan 12, 2009
Messages
344
Office Version
  1. 2019
Platform
  1. Windows
I have a simple spreadsheet that looks like the following...

Product 1Product 2Product 3Product 4
Item 11.4%1.4%1.4%
Item 25.6%7.7%
Item 36.7%
Item 41.2%
Item 52.1%
Item 65.5%

<tbody>
</tbody>

I wanted to have the ability to have a dynamic list generated where the user chooses a product (via a dropdown list called "Portfolio" and a list generates where it will give the list of the Item Number and number associated with it. Like...

Product 2
Item 1 1.4%
Item 2 5.6%
Item 5 2.1%

Product 4
Item 2 7.7%
Item 4 1.2%

Is this possible and how would I tackle this?

Cheers,

Guy
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Create your dropdown using DataValidation in a particular cell off to the side of your other data. Copy the following code to the worksheet change event, and make sure the 3 lines near the top are identifying the right cell values for the item and product cells as well as the dropdown cell. The list will be copied in the cell below the dropdown cell as a copy of the data, and blanks will be removed whenever that dropdown cell is changed.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dataValidationCell As Range
    Dim productColumn As Range
    Dim productHeaders As Range
    Dim itemList As Range
    Dim last As Range
    Dim l As Long
    
    '****************************
    'Change these values if necessary to the location on your Sheet
    '****************************
    Set itemList = Range("A2")  'the first, top cell of the items
    Set productHeaders = Range("B1")  'the first, left cell of the products
    Set dataValidationCell = Range("H1")  'the cell of the dropdown
    '****************************
    
    If Target.Address = dataValidationCell.Address Then
        Set last = itemList.End(xlDown)
        Set itemList = itemList.Resize(last.Row - itemList.Row + 1, 1)
        Set productHeaders = Range(productHeaders, productHeaders.End(xlToRight))
    
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        On Error Resume Next
        Set productColumn = productHeaders.Find(Target.Value)
        On Error GoTo 0
        If Not productColumn Is Nothing Then
            itemList.Copy Target.Offset(1, 0).Resize(itemList.Rows.Count, 1)
            productColumn.Offset(1, 0).Resize(itemList.Rows.Count, 1).Copy Target.Offset(1, 1).Resize(itemList.Rows.Count, 1)
            For l = Target.Offset(itemList.Rows.Count, 1).Row To Target.Offset(1, 1).Row Step -1
                If Cells(l, Target.Column + 1).Value = "" Then
                    Cells(l, Target.Column).Resize(1, 2).Delete xlShiftUp
                End If
            Next l
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

I got this working on my end, so let me know if it doesn't work for you.
 
Upvote 0
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
1​
Product 1Product 2Product 3Product 4Product 1Product 2
2​
Item 1
1.40%​
1.40%​
1.40%​
0.014​
0.014​
3​
Item 2
5.60%​
7.70%​
0.055​
0.056​
4​
Item 3
6.70%​
0.021​
5​
Item 4
1.20%​
6​
Item 5
2.10%​
7​
Item 6
5.50%​

In H2 control+shift+enter, not just enter, copy across, and down:
Rich (BB code): 
=IFERROR(INDEX($B$2:$E$7,SMALL(IF(ISNUMBER(INDEX($B$2:$E$7,0,MATCH(H$1,$B$1:$E$1,0))),
    ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(H$2:H2)),MATCH(H$1,$B$1:$E$1,0)),"")
 
Upvote 0
Thanks for your reply. I tried the code but instead of pasting the results in a new column it pastes the results over column A & B. I would like the new list to start in a different column, say "G". How would I offset that?

Cheers!

Guy

shknbk2 said:
Create your dropdown using DataValidation in a particular cell off to the side of your other data. Copy the following code to the worksheet change event, and make sure the 3 lines near the top are identifying the right cell values for the item and product cells as well as the dropdown cell. The list will be copied in the cell below the dropdown cell as a copy of the data, and blanks will be removed whenever that dropdown cell is changed.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dataValidationCell As Range
    Dim productColumn As Range
    Dim productHeaders As Range
    Dim itemList As Range
    Dim last As Range
    Dim l As Long
    
    '****************************
    'Change these values if necessary to the location on your Sheet
    '****************************
    Set itemList = Range("A2")  'the first, top cell of the items
    Set productHeaders = Range("B1")  'the first, left cell of the products
    Set dataValidationCell = Range("H1")  'the cell of the dropdown
    '****************************
    
    If Target.Address = dataValidationCell.Address Then
        Set last = itemList.End(xlDown)
        Set itemList = itemList.Resize(last.Row - itemList.Row + 1, 1)
        Set productHeaders = Range(productHeaders, productHeaders.End(xlToRight))
    
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        On Error Resume Next
        Set productColumn = productHeaders.Find(Target.Value)
        On Error GoTo 0
        If Not productColumn Is Nothing Then
            itemList.Copy Target.Offset(1, 0).Resize(itemList.Rows.Count, 1)
            productColumn.Offset(1, 0).Resize(itemList.Rows.Count, 1).Copy Target.Offset(1, 1).Resize(itemList.Rows.Count, 1)
            For l = Target.Offset(itemList.Rows.Count, 1).Row To Target.Offset(1, 1).Row Step -1
                If Cells(l, Target.Column + 1).Value = "" Then
                    Cells(l, Target.Column).Resize(1, 2).Delete xlShiftUp
                End If
            Next l
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

I got this working on my end, so let me know if it doesn't work for you.
Click to expand...
 
Upvote 0
The code should have pasted the data starting in the cell below the "dataValidationCell" address, which in the stock code was H1. However, it uses the Target address that should have been the same as the dataValidationCell address. But, since it is pasting the data somewhere else, the code below substitutes dataValidationCell for Target:
Code: 
        On Error Resume Next
        Set productColumn = productHeaders.Find([COLOR=#574123]dataValidationCell[/COLOR].Value)
        On Error GoTo 0
        If Not productColumn Is Nothing Then
            itemList.Copy [COLOR=#574123]dataValidationCell[/COLOR].Offset(1, 0).Resize(itemList.Rows.Count, 1)
            productColumn.Offset(1, 0).Resize(itemList.Rows.Count, 1).Copy [COLOR=#574123]dataValidationCell[/COLOR].Offset(1, 1).Resize(itemList.Rows.Count, 1)
            For l = [COLOR=#574123]dataValidationCell[/COLOR].Offset(itemList.Rows.Count, 1).Row To [COLOR=#574123]dataValidationCell[/COLOR].Offset(1, 1).Row Step -1
                If Cells(l, [COLOR=#574123]dataValidationCell[/COLOR].Column + 1).Value = "" Then
                    Cells(l, [COLOR=#574123]dataValidationCell[/COLOR].Column).Resize(1, 2).Delete xlShiftUp
                End If
            Next l
        End If
 
Upvote 0
Aladin, thank you for the response. I got the info to work per your code but the problem is that the new list doesn't bring over the "Item" info along with the percentages. I use A1 as a Data Validation drop down list of Product 1...Product 4. User chooses a Product number then I want a list to show in Column H and I that lists "Item" in column H and the corresponding percentage for that chosen Product in row I. The goal is to just show one Product at a time instead of all of them. Does that make sense? Like This

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
1​
DROP DOWN (choose Product 2)Product 1Product 2Product 3Product 4Items%
2​
Item 1
1.40%​
1.40%​
1.40%​
Item 1​
0.014​
3​
Item 2
5.60%​
7.70%​
Item 2​
0.056​
4​
Item 3
6.70%​
Item 4
0.021​
5​
(blank row)

​
6​
Item 4
2.10%​
7​
Item 5
5.50%​

<tbody>
</tbody>



Aladin Akyurek said:
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
1​
Product 1Product 2Product 3Product 4Product 1Product 2
2​
Item 1
1.40%​
1.40%​
1.40%​
0.014​
0.014​
3​
Item 2
5.60%​
7.70%​
0.055​
0.056​
4​
Item 3
6.70%​
0.021​
5​
Item 4
1.20%​
6​
Item 5
2.10%​
7​
Item 6
5.50%​

<tbody>
</tbody>


In H2 control+shift+enter, not just enter, copy across, and down:
Rich (BB code): 
=IFERROR(INDEX($B$2:$E$7,SMALL(IF(ISNUMBER(INDEX($B$2:$E$7,0,MATCH(H$1,$B$1:$E$1,0))),
    ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(H$2:H2)),MATCH(H$1,$B$1:$E$1,0)),"")
Click to expand...
 
Upvote 0
Now that I see your actual implementation, the setup isn't quite what I envisioned. I've redone the code to have the datavalidation cell be in A1 while the list of the products starts in H2. See if this works.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dataValidationCell As Range
    Dim productColumn As Range
    Dim productHeaders As Range
    Dim listCell as Range
    Dim itemList As Range
    Dim last As Range
    Dim l As Long
    
    '****************************
    'Change these values if necessary to the location on your Sheet
    '****************************
    Set itemList = Range("A2")  'the first, top cell of the items
    Set productHeaders = Range("B1")  'the first, left cell of the products
    Set dataValidationCell = Range("A1")  'the cell of the dropdown
    Set listCell = Range("H2")   'first cell of the location where the list goes
    '****************************
    
    If Target.Address = dataValidationCell.Address Then
        Set last = itemList.End(xlDown)
        Set itemList = itemList.Resize(last.Row - itemList.Row + 1, 1)
        Set productHeaders = Range(productHeaders, productHeaders.End(xlToRight))
    
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        On Error Resume Next
        Set productColumn = productHeaders.Find(dataValidationCell.Value)
        On Error GoTo 0
        If Not productColumn Is Nothing Then
            itemList.Copy listCell.Resize(itemList.Rows.Count, 1)
            productColumn.Offset(1, 0).Resize(itemList.Rows.Count, 1).Copy listCell.Resize(itemList.Rows.Count, 1)
            For l = listCell.Offset(itemList.Rows.Count - 1, 1).Row To listCell.Row Step -1
                If Cells(l, listCell.Column + 1).Value = "" Then
                    Cells(l, listCell.Column).Resize(1, 2).Delete xlShiftUp
                End If
            Next l
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
 
Upvote 0
Thanks for the suggestion. I copied your code but when I change A1 no information is pasted into H2 (and below). The VBA code starts but doesn't do anything. Am I mising something? Thanks for your help

Guy

shknbk2 said:
Now that I see your actual implementation, the setup isn't quite what I envisioned. I've redone the code to have the datavalidation cell be in A1 while the list of the products starts in H2. See if this works.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dataValidationCell As Range
    Dim productColumn As Range
    Dim productHeaders As Range
    Dim listCell as Range
    Dim itemList As Range
    Dim last As Range
    Dim l As Long
    
    '****************************
    'Change these values if necessary to the location on your Sheet
    '****************************
    Set itemList = Range("A2")  'the first, top cell of the items
    Set productHeaders = Range("B1")  'the first, left cell of the products
    Set dataValidationCell = Range("A1")  'the cell of the dropdown
    Set listCell = Range("H2")   'first cell of the location where the list goes
    '****************************
    
    If Target.Address = dataValidationCell.Address Then
        Set last = itemList.End(xlDown)
        Set itemList = itemList.Resize(last.Row - itemList.Row + 1, 1)
        Set productHeaders = Range(productHeaders, productHeaders.End(xlToRight))
    
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        On Error Resume Next
        Set productColumn = productHeaders.Find(dataValidationCell.Value)
        On Error GoTo 0
        If Not productColumn Is Nothing Then
            itemList.Copy listCell.Resize(itemList.Rows.Count, 1)
            productColumn.Offset(1, 0).Resize(itemList.Rows.Count, 1).Copy listCell.Resize(itemList.Rows.Count, 1)
            For l = listCell.Offset(itemList.Rows.Count - 1, 1).Row To listCell.Row Step -1
                If Cells(l, listCell.Column + 1).Value = "" Then
                    Cells(l, listCell.Column).Resize(1, 2).Delete xlShiftUp
                End If
            Next l
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
Click to expand...
 
Upvote 0
New code with a few tweaks. I hadn't tried the corrections before, but the code below should now work.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dataValidationCell As Range
    Dim productColumn As Range
    Dim productHeaders As Range
    Dim listCell As Range
    Dim itemList As Range
    Dim last As Range
    Dim l As Long
    
    '****************************
    'Change these values if necessary to the location on your Sheet
    '****************************
    Set itemList = Range("A2")  'the first, top cell of the items
    Set productHeaders = Range("B1")  'the first, left cell of the products
    Set dataValidationCell = Range("A1")  'the cell of the dropdown
    Set listCell = Range("H2")   'first cell of the location where the list goes
    '****************************
    
    If Target.Address = dataValidationCell.Address Then
        Set last = Cells(Rows.Count, itemList.Column).End(xlUp)
        Set itemList = itemList.Resize(last.Row - itemList.Row + 1, 1)
        Set productHeaders = Range(productHeaders, productHeaders.End(xlToRight))
    
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        On Error Resume Next
        Set productColumn = productHeaders.Find(dataValidationCell.Value)
        On Error GoTo 0
        If Not productColumn Is Nothing Then
            itemList.Copy listCell.Resize(itemList.Rows.Count, 1)
            productColumn.Offset(1, 0).Resize(itemList.Rows.Count, 1).Copy listCell.Offset(0, 1).Resize(itemList.Rows.Count, 1)
            For l = listCell.Offset(itemList.Rows.Count - 1, 1).Row To listCell.Row Step -1
                If Cells(l, listCell.Column + 1).Value = "" Then
                    Cells(l, listCell.Column).Resize(1, 2).Delete xlShiftUp
                End If
            Next l
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
 
Last edited:
Upvote 0
GuyGadois said:
Aladin, thank you for the response. I got the info to work per your code but the problem is that the new list doesn't bring over the "Item" info along with the percentages. I use A1 as a Data Validation drop down list of Product 1...Product 4. User chooses a Product number then I want a list to show in Column H and I that lists "Item" in column H and the corresponding percentage for that chosen Product in row I. The goal is to just show one Product at a time instead of all of them. Does that make sense? Like This

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
I​
1​
DROP DOWN (choose Product 2)Product 1Product 2Product 3Product 4Items%
2​
Item 1
1.40%​
1.40%​
1.40%​
Item 1​
0.014​
3​
Item 2
5.60%​
7.70%​
Item 2​
0.056​
4​
Item 3
6.70%​
Item 4
0.021​
5​
(blank row)

​
6​
Item 4
2.10%​
7​
Item 5
5.50%​

<tbody>
</tbody>
Click to expand...

In H2 control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX($A$2:$A$7,SMALL(IF(ISNUMBER(INDEX($B$2:$E$7,0,MATCH(A$1,$B$1:$E$1,0))),
    ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(H$2:H2))),"")

In I2 control+shif+enter, not just enter, and copy down:
Rich (BB code): 
=IF($H2="","",INDEX($B$2:$E$7,SMALL(IF(ISNUMBER(INDEX($B$2:$E$7,0,MATCH(A$1,$B$1:$E$1,0))),
    ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(I$2:I2)),MATCH(A$1,$B$1:$E$1,0)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
create a report summary page by group
Replies
4
Views
116
PippaThePointer
P
G
  • Question
Creating a dynamic list of unique values in a range with a condition
Replies
3
Views
187
Cubist
Cubist
J
  • Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
553
juliecooper255
J
E
  • Question
Write Macro to highlight based on values in another sheet
Replies
3
Views
202
Peter_SSs
Peter_SSs
C
  • Question
Calculate top 70% based on total spend
Replies
2
Views
267
StephenCrump
S

Forum statistics

Threads
1,221,470
Messages
6,160,029
Members
451,611
Latest member
PattiButche

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top