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...

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 3[/TD]
[TD]Product 4[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]1.4%[/TD]
[TD]1.4%[/TD]
[TD]1.4%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD][/TD]
[TD]5.6%[/TD]
[TD][/TD]
[TD]7.7%
[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]6.7%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1.2%[/TD]
[/TR]
[TR]
[TD]Item 5[/TD]
[TD][/TD]
[TD]2.1%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 6[/TD]
[TD]5.5%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][/tr][tr][td]
1​
[/td][td][/td][td]Product 1[/td][td]Product 2[/td][td]Product 3[/td][td]Product 4[/td][td][/td][td][/td][td]Product 1[/td][td]Product 2[/td][/tr]
[tr][td]
2​
[/td][td]Item 1[/td][td]
1.40%​
[/td][td]
1.40%​
[/td][td]
1.40%​
[/td][td][/td][td][/td][td][/td][td]
0.014​
[/td][td]
0.014​
[/td][/tr]
[tr][td]
3​
[/td][td]Item 2[/td][td][/td][td]
5.60%​
[/td][td][/td][td]
7.70%​
[/td][td][/td][td][/td][td]
0.055​
[/td][td]
0.056​
[/td][/tr]
[tr][td]
4​
[/td][td]Item 3[/td][td][/td][td][/td][td]
6.70%​
[/td][td][/td][td][/td][td][/td][td][/td][td]
0.021​
[/td][/tr]
[tr][td]
5​
[/td][td]Item 4[/td][td][/td][td][/td][td][/td][td]
1.20%​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
6​
[/td][td]Item 5[/td][td][/td][td]
2.10%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
7​
[/td][td]Item 6[/td][td]
5.50%​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]DROP DOWN (choose Product 2)[/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 3[/TD]
[TD]Product 4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Items[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Item 1[/TD]
[TD]
1.40%​
[/TD]
[TD]
1.40%​
[/TD]
[TD]
1.40%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Item 1​
[/TD]
[TD]
0.014​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Item 2[/TD]
[TD][/TD]
[TD]
5.60%​
[/TD]
[TD][/TD]
[TD]
7.70%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Item 2​
[/TD]
[TD]
0.056​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]Item 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]
6.70%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD]
0.021​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD](blank row)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]Item 4[/TD]
[TD][/TD]
[TD]
2.10%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]Item 5[/TD]
[TD]
5.50%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Aladin Akyurek said:
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD][/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 3[/TD]
[TD]Product 4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Item 1[/TD]
[TD]
1.40%​
[/TD]
[TD]
1.40%​
[/TD]
[TD]
1.40%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
0.014​
[/TD]
[TD]
0.014​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Item 2[/TD]
[TD][/TD]
[TD]
5.60%​
[/TD]
[TD][/TD]
[TD]
7.70%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
0.055​
[/TD]
[TD]
0.056​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]Item 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]
6.70%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
0.021​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]Item 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1.20%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]Item 5[/TD]
[TD][/TD]
[TD]
2.10%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]Item 6[/TD]
[TD]
5.50%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]DROP DOWN (choose Product 2)[/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 3[/TD]
[TD]Product 4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Items[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Item 1[/TD]
[TD]
1.40%​
[/TD]
[TD]
1.40%​
[/TD]
[TD]
1.40%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
Item 1​
[/TD]
[TD]
0.014​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Item 2[/TD]
[TD][/TD]
[TD]
5.60%​
[/TD]
[TD][/TD]
[TD]
7.70%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Item 2​
[/TD]
[TD]
0.056​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]Item 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]
6.70%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD]
0.021​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD](blank row)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]Item 4[/TD]
[TD][/TD]
[TD]
2.10%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]Item 5[/TD]
[TD]
5.50%​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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

K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
193
krunal123
K
P
  • Solved
create a report summary page by group
Replies
4
Views
203
PippaThePointer
P
G
  • Question Question
Creating a dynamic list of unique values in a range with a condition
Replies
3
Views
206
Cubist
Cubist
J
  • Question Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
673
juliecooper255
J
E
  • Question Question
Write Macro to highlight based on values in another sheet
Replies
3
Views
287
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,948
Messages
6,175,573
Members
452,652
Latest member
eduedu

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