Custom Vlookup Function

[cla99009]

I have pretty extensive sub programming experience, but I've had only modest
success writing custom functions as they take slightly different parameters.

I have a workbook with several tabs, one for each month (Jan 08, Feb 08,
etc), and an output tab. Each tab contains a report of the total payroll for
each of up to a few hundred employees for that month. I need the output tab
to be a pivot table of sorts, with each employee's name and number in rows
down columns A and B. I want columns C, D, E, and so on, to output the net
payroll for each employee for each month. I have a complex string of a
vlookup that does that already in place, but I have to change the vlookup to
the appropriate month as well as build into it several if(isna()) functions
to return a 0 if a given employee has no payroll in a given month. I would
like to build a custom function that will look something like this:

Function LookupPay(EmployeeNumber as string, MonthTab as string)

I'm having trouble finding the correct syntax that will take the MonthTab as
an imput and use that to designate the correct sheet.

Any help would be greatly appreciated.

 

[Erin Searfoss]

If I understand correctly, in this instance you don't need to write a custom
function. Instead you can use the INDIRECT function of Excel.

EmpID Jan 08 Feb 08 Mar 08
555 654 5683 564
444 78 6546 78
333 54 8686 55
222 654 3546 5348


where cell B2 contains this formula:

=VLOOKUP($A2,INDIRECT("'"&B$1&"'!PayChart"),5,0)

In this example the column headers are entered as text so that they match
exactly the sheet names. Each month sheet contains a range named PayChart of
which the employee ID's are in the first column and the net pay in the 5th
column. The use of INDIRECT in this case gives the same result as
=VLOOKUP($A2,'Jan 08'!PayChart,5,0)

Hope this helps.

Erin

 

[cla99009]

Cool. I've never used the indirect function before. It worked perfectly. I
still would like to learn how to perfect the custom function thing.

Thanks!

 

[Erin Searfoss]

Chapter 10 of John Walkenbach's Excel 2003 Power Programming with VBA has a
pretty good discussion. I assume the same is true of the Excel 2007 version.
I recommend the rest of the book as well.