User defined function to store conditional Vlookup formula needed

[kelly mort]

I have 3 sheets that I am using Vlookup formula to pull data from based on a condition in a cell; A2 on Sheet4.

And the conditions are A, B, C.

If A, then I am looking up the value in B2 on Sheet4 from sheet1 range A2:D30 . If B then same value in B2 Sheet4 but now on sheet2 in that order. I have the formula running. Now I am thinking of a way to use a user defined function for the formula.

That is store the formula inside the udf and call it when I need it instead of having the formula always in the workbook.

I am only looking for the udf- I can do the calling.

I am trying to find a way to avoid too many formulas .

If there is a cooler way to get this done, I will want to know. Thanks.

 

[Worf]

Something like this:

Function kelly()
Dim s$
Select Case Sheets("sheet4").[a2]
    Case "A": s = "sheet1"
    Case "B": s = "sheet2"
    Case "C": s = "sheet3"
End Select
kelly = Evaluate("=vlookup(sheet4!b2," & s & "!a2:d20,4,false)")
End Function

 

[kelly mort]

Great great!!!!
Cooler than I ever imagined.

One more demand :

Can the 4 on the line ......

...., 4, false)")

Be set to increase as I drag the formula or function down?

That is I want start the function from A4 on Sheet4 down to A7. So we begin the Vlookup from ...., 1, False )") up to ....., 4, false)")

Thanks

 

[Worf]

This is based on the row where the formula is:

Function kelly()
Dim s$
Const os As Integer = 3                                 ' desired offset
Select Case Sheets("sheet4").[a2]
    Case "A": s = "sheet1"
    Case "B": s = "sheet2"
    Case "C": s = "sheet3"
End Select
kelly = Evaluate("=vlookup(sheet4!b2," & s & "!a2:d20," & _
(Application.Caller.Row - os) & ",false)")
End Function<strike></strike>

 

[kelly mort]

Very brilliant fix!!!!!

I have set the calculations to automatic however when I change the B2 value on Sheet4 it does not lookup automatically. I have to edit the formula before it does. If this problem too has an antidote then I will be glad to have it.

Regards
Kelly

 

[Dave Patton]

You could try Excel's Choose instead of a UDF.

Excel 2010
	A	B	C	D	E
2	A	22			A
3		22			B
1b

Cell Formulas
Range	Formula
B2	=CHOOSE(MATCH(A2,E2:E3,0),VLOOKUP(A2,Sheet1!A2:D20,4,0),VLOOKUP(A2,Sheet2!A2:D20,4,0))
B3	=CHOOSE(MATCH(A2,{"A";"B"},0),VLOOKUP(A2,Sheet1!A2:D20,4,0),VLOOKUP(A2,Sheet2!A2:D20,4,0))

 

[kelly mort]

You could try Excel's Choose instead of a UDF.

Excel 2010

	A	B	C	D	E
A	A
B

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

1b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=CHOOSE(MATCH(A2,E2:E3,0),VLOOKUP(A2,Sheet1!A2:D20,4,0),VLOOKUP(A2,Sheet2!A2:D20,4,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=CHOOSE(MATCH(A2,{"A";"B"},0),VLOOKUP(A2,Sheet1!A2:D20,4,0),VLOOKUP(A2,Sheet2!A2:D20,4,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Cool. But the only issue is that I don't know how to call this with a vba script. Because I will only use the formula at certain times in the process.

Can you point how I shall call it with a vba code?

Thanks

 

[Worf]

Very brilliant fix!!!!!

I have set the calculations to automatic however when I change the B2 value on Sheet4 it does not lookup automatically. I have to edit the formula before it does. If this problem too has an antidote then I will be glad to have it.

Regards
Kelly

' sheet 4 module
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1:b2]) Is Nothing Then _
Application.CalculateFull
End Sub

 

[kelly mort]

' sheet 4 module
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.[a1:b2]) Is Nothing Then _
Application.CalculateFull
End Sub

Okay thanks