Forum Discussion
Help With Vlookup/Index with lists
Right-click the sheet tab and select 'View Code' from the context menu to view the code in the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Long Dim c As Long Dim s As Long If Not Intersect(Range("H2"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Range("I2,M2:M18").ClearContents s = 1 If Range("H2").Value <> "" Then c = Application.Match(Range("H2").Value, Range("A1:F1"), 0) For r = 2 To 18 If Cells(r, c).Value <> "" Then s = s + 1 Cells(s, 13).Value = Cells(r, 1).Value End If Next r End If Application.EnableEvents = True Application.ScreenUpdating = True End If End SubThis is a so-called event procedure. It will run automatically whenever you change the value of one or more cells.
The code first checks whether H2 is among the changed cells.
If so, it temporarily freezes the display. It also disables further event handling, to prevent the code from causing a cascade of events when it changes other cells.
It then clears I2 because selecting a different diameter will invalidate the currently selected length.
It also clears the range M2:M18. This range is used to list the available lengths.
If H2 has been cleared by the user, we're done.
But if a diameter has been selected, the code finds the column number of that diameter. It is assigned to the variable c.
The code loops through rows 2 to 18 in that column, and if the cell in that row is not empty, its value is added to column M.
At the end of the loop, column M contains the available lengths.
Finally, event handling and screen updating are restored.
Back in Excel, select I2, then click Data Validation on the Data tab of the ribbon.
You'll see that the source of the drop-down list is
=OFFSET($M$1,1,0,COUNTA($M:$M)-1,1)
This is the range starting one row down from M1, i.e. in M2, and going down as many rows as there are non-empty cells minus one (to exclude the header cell).
This is dynamic, so the source will grow and shrink when you select a different diameter.
Here is a solution that should work in Excel 2016. The workbook is now a macro-enabled workbook, so you'll have to allow macros.
- That does seem to work. Thank you! Any chance you can explain how you did it so i can move the results and apply it to the ranges too?
Right-click the sheet tab and select 'View Code' from the context menu to view the code in the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Long Dim c As Long Dim s As Long If Not Intersect(Range("H2"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Range("I2,M2:M18").ClearContents s = 1 If Range("H2").Value <> "" Then c = Application.Match(Range("H2").Value, Range("A1:F1"), 0) For r = 2 To 18 If Cells(r, c).Value <> "" Then s = s + 1 Cells(s, 13).Value = Cells(r, 1).Value End If Next r End If Application.EnableEvents = True Application.ScreenUpdating = True End If End SubThis is a so-called event procedure. It will run automatically whenever you change the value of one or more cells.
The code first checks whether H2 is among the changed cells.
If so, it temporarily freezes the display. It also disables further event handling, to prevent the code from causing a cascade of events when it changes other cells.
It then clears I2 because selecting a different diameter will invalidate the currently selected length.
It also clears the range M2:M18. This range is used to list the available lengths.
If H2 has been cleared by the user, we're done.
But if a diameter has been selected, the code finds the column number of that diameter. It is assigned to the variable c.
The code loops through rows 2 to 18 in that column, and if the cell in that row is not empty, its value is added to column M.
At the end of the loop, column M contains the available lengths.
Finally, event handling and screen updating are restored.
Back in Excel, select I2, then click Data Validation on the Data tab of the ribbon.
You'll see that the source of the drop-down list is
=OFFSET($M$1,1,0,COUNTA($M:$M)-1,1)
This is the range starting one row down from M1, i.e. in M2, and going down as many rows as there are non-empty cells minus one (to exclude the header cell).
This is dynamic, so the source will grow and shrink when you select a different diameter.