VLookup with excel multiple columns

[cubstyled]

I have a work book all data validation is on sheet1 and sheet2 holds two combo boxes that validate against each other through vba. I am trying to do a vlookup that validates to 2 named ranges incompasing 2 columns per name. I want to do this as aposed to puting everything in just two columns and naming the whole range OpRate to keep my sheet from being thousands of lines.
=Sheet1!$C$1:$D$116 ----- Named DneedleRate
=Sheet1!$E$1:$F$35 -------Named ZigZagRate

I want column "E" on sheet two to vlookup c2 and validate against the named ranges
I currently have all data in two columns named OpRate and it is getting out of hand ( length) and I am using (this is working, just to stinking long for records)

=IF(ISNA(VLOOKUP(C2,OpRate,2,0)),"",VLOOKUP(C2,OpRate,2,0))

There will eventualy be about 15-20 named ranges I would like to vlookup against

Thanks for any and all help

 

[SkipVought]

You can do an UPDATE query using MS Query to update your table in your Excel workbook. It could be that workbook or another workbook that contains the table to update. If your table is in another workbook, it does not need to be open to access it via a query.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[cubstyled]

Skip
That's exactly what I am working on now. I am creating a workbook to do a form off of and I will update the other workbook via a macro when it is opened

cubstyled

 

[cubstyled]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("ValidationList")
Cancel = True
Set cboTemp = ws.OLEObjects("GroupCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
If Target.Column = 4 Then
str = "=" & Target.Offset(0, -2).Value
Else
str = Target.Validation.Formula1
End If
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("GroupCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

 

[SkipVought]

It might be better then to close this thread and open a new one, if needed, in the VBA Visual Basic for Applications (Microsoft)

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[cubstyled]

not a problem

cub