Help With Vlookup/Index with lists

[mikec5000]

Hi,

I have a list of Diameters, selectable from a Data Validation List from the options in row 1.
A list of lengths, also selectable from a data validation list from the lenghts in column A.

What im trying to do is, Once the diameted has been selected in H2, The list in I2 will only show lengths that have a cell thats not blank (with a range) Is this possible?

J2 is currently using this formula:

=INDEX($A1:$F18,MATCH(I2,$A1:$A18,0),MATCH(H2,$A$1:$F$1,0))

H2 and I2 are just lists set up in the data validation tab

Even if the diameter was selected and a list of lengths and ranges could be printed omitting the blanks, that would be great.

[#2]

@mikec5000 

If you have Microsoft 365 or Office 2021, you can use a helper range with the FILTER function.

See the attached sample workbook.

[#3]

Unfortunately my work only has Office 2016, but thanks, the theory works

[#4]

Even if selecting the diameter would list all the lengths with values and the associated ranges it would work

[#5]

@mikec5000 

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.

[#6]

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?

[#7 · Accepted solution]

@mikec5000 

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 Sub

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

[#8]

That is perfect! thank you so much for your help!!