PDA

View Full Version : Offsetting Multiple VLookups with Picklists



kantian1
10-26-2008, 06:38 AM
Hi All,

Have a look at the file I have attached. I am trying to use 2 picklists and 2 Vlookups so that if the 1st Picklist is selected I get the result in the 2nd Picklist and visa versa if the 2nd Picklist is selected I get the result in the 1st Picklist.

Thank for your help.

mdmackillop
10-26-2008, 06:58 AM
Not without VBA, or you would create a circular loop.

Bob Phillips
10-26-2008, 07:02 AM
Couldn't you just blank out one or the other and use

=IF(B3<>"",formula1,IF(B4<>"",formula2,""))

kantian1
10-26-2008, 07:05 AM
Hi xld,

Can you put your idea in the attached file so I can see what you mean?

Thanks.

Bob Phillips
10-26-2008, 07:06 AM
I would need more from you before I can do that as I was not clear what you would be looking up from B3 and/or B4.

kantian1
10-26-2008, 07:12 AM
You've lost me, can't you just use the a,b,c and 1,2,3 data in the table?

Thanks.

Bob Phillips
10-26-2008, 07:52 AM
Use it where, how?

kantian1
10-26-2008, 08:00 AM
Hi,

Maybe our wires are crossed. If I select 'a' from the first Picklist I get '1' in the second Picklist, and the same with 'b' I get '2' etc.

As well as this I want the ability to select any of the numbers from the second Picklist, like '3' and get the result 'c' in the first Picklist.

Thanks.

georgiboy
10-26-2008, 08:07 AM
So let me get this straight you want it so that when you select "e" in the first picklist you want the vlookup formula to display "5" and if you selected say "6" in the second picklist the vlookup formula would display "f", is that correct?

georgiboy
10-26-2008, 08:11 AM
oh i see, i believe as XLD said you would get a circular reference. What is the use of the Vlookup then?

kantian1
10-26-2008, 08:18 AM
Hi,

Is there no way of putting the formulas somewhere else, so the formulas are in different cells to the results?

Thanks.

Bob Phillips
10-26-2008, 08:21 AM
Then you need VBA event code as MD suggested



Private Sub Worksheet_Change(ByVal Target As Range)
Dim idx As Long

Application.EnableEvents = False

If Target.Address = "$B$3" Then

idx = Application.Match(Target.Value, Me.Range(Right$(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)), 0)
Me.Range("B4").Value = Application.Index(Me.Range(Right$(Range("B4").Validation.Formula1, Len(Range("B4").Validation.Formula1) - 1)), idx)
ElseIf Target.Address = "$B$4" Then

idx = Application.Match(Target.Value, Me.Range(Right$(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)), 0)
Me.Range("B3").Value = Application.Index(Me.Range(Right$(Range("B3").Validation.Formula1, Len(Range("B4").Validation.Formula1) - 1)), idx)
End If

Application.EnableEvents = True

End Sub

georgiboy
10-26-2008, 08:23 AM
Not without VBA, or you would create a circular loop.

I meant to say MD sorry :blush

kantian1
10-26-2008, 08:27 AM
Hi xld,

That works perfect, but due to restrictions I can't use VBA. Can there possibly be answer without using VBA?

Thanks.