Dynamic Data Validation

Hi,

I am trying to use a VLOOKUP scenario for a data validation but I can't get it to work. I have looked at the Contextures information but cant decipher what I would like to do. In the contextures it shows all the lists as separate - can you not just amalgamate them?

I would like the data validation list to be limited based upon another cell looking at a table. Is this possible?

=OFFSET($G$2,0,1,MATCH($D$1,$G$2:$G$8,1),1)

I am trying to return the values in column H:H where D1 matched column G.

TIH
Matthew

I've used a small macro to do this. See my attachement. that will clear things up. Change D1 and/orE1. Vallist is a 'named' range.

Good luck

Sub ValList()

Dim ValListStr As String
Dim Ccell As Range

For Each Ccell In Range("vallist").Resize(, 1) 'only left
  If Ccell = Range("D1") Then
    ValListStr = ValListStr & "," & Ccell.Offset(0, 1)
  End If
Next
With Selection.Validation
  .Delete
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  xlBetween, Formula1:=ValListStr
  .IgnoreBlank = True
  .InCellDropdown = True
End With

This is triggerd by

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$E$1" Then
  Call ValList
End If

End Sub