On Jan 21, 3:19 pm, Gemz wrote:
I posted the below on the excel worksheet function forum and was advised to
post he please see two queries below.

Thanks.

1)
I have the following formula:

=VLOOKUP(B5,INDIRECT("'"&INDEX(WSlist,MATCH(1,--(COUNTIF(INDIRECT("'"&WSlist&"'!E:E"),B5)0),0))&" '!E:E"),1,0)

It works fine but i was wondering if there is a way to get it to work as
part of a macro? If i leave it as a formula then this means that the same
sheet will have to be copied across into each new months file, i would like a
button to just do the job
instead - find the info required and put it in a new sheet that it will
automatically
be re-named "lookup". is this possible?

2)
i am looking to do another type of lookup -i have 3 different sources of
information and i have to do 3 different lookups between each source to find
out which staff member is on each:

whose on source 1 and not on source 2
whose on source 2 and not on source 1
who is on source 1 that is also on source 3

is there a quicker way around this i macro? or would it jus be 3 different
lookups?

thanks a lot for your help, really appreciate it.

Gemz,

Yeah, you can do it in VBA using the WorksheetFunction method. It
starts off like this:

someVariable = WorkSheetFunction.("VLookUp(...

And then you have to fill in all the parameters to complete the
statement. Embedding multiple WorksheetFunctions into a single VB
statement can be a bear, so it's better to calculate intermediate
values in separate VB statements using the results from one as the
parameters for the next.

To use the marco the way you intend really requires you to create a VB
function rather as sub i.e.

Public Function NewFunction(parameter1, parameter2, ...) as Variable
Type

Your code here...

End Function

And pass your parameters to the function and have the function return
the calculated value back to the target cell.

When you create a user defined function, it will appear in the
function list and selecting it will reveal a standard function dialog
box with an entry box for each parameter that you defined. Ranges are
accepted too like with regular functions.

If you want to know more, suggest you hunt around the net for advice
on building functions. But they are pretty easy to work with.

Good Luck,

SteveM