if there is a way to get my formula to work
as part of a macro or a vb code?

I'm sure this could be done with some VBA code.

Post in the programming forum.

--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
Sorry to bother you again but i just wanted to ask if there is a way to
get
my formula to work as part of a macro or a vb code? 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?

also, i am looking to do another type of lookup only this time 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? or would it jus be 3 different
lookups?

thanks a lot for your help, really appreciate it.


"T. Valko" wrote:

I tested the formula on a file with 32 sheets and it worked ok.

Is the defined name WSList a static range or is it dynamic? If it's
dynamic
you'll get a #REF! error because INDIRECT won't interpret this correctly.
Are there any empty cells in WSList? If a sheet does not exist (yet) but
the
sheet name is in WSList then you'll get a #REF! error.

what i need it for (which are 31 sheets)

31 sheets for the days of the month? If so, what are the sheet names? 1,
2,
3 .... 31? If the sheet names follow some sort of sequential pattern you
can
eliminate having to create the WSList.


--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
Thanks for the new formula, it worked when i tested it on 3 sheets but
when i
tried to make it work for what i need it for (which are 31 sheets) it
doesnt
work!

This is the formula i changed to new cell references:

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&WSlist&"'!D:D" ),A2)),A2,"")

I just keep getting "REF!". I have the sheet names re-named in the
exact
way
they appear on the sheet tabs and they are in the named range "WSList",
the
data that i am looking up is in column A (which is in the sheet where i
am
doing the formula which is an unnamed sheet1) and in all the other
sheets
i
need to look in column D.

what am i doing wrong?

please help. thanks.

"T. Valko" wrote:

when i change it to "1,0" it doesnt work!

Hmmm...

It works for me.

However, if all you want to do is verify that the name appears on some
other
sheet try this instead (normally entered, not array entered although
it'll
work either way):

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&wslist&"'!A:A" ),A2)),A2,"")

--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
I have now used the formula from your example sheet:

=VLOOKUP(A2,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSList&"'!A:A"),A2)0,0))&"'!A :B"),2,0)

This works ok, expect the formula returns back information that i
dont
really need, i understand this is because of the "2,0" but when i
change
it
to "1,0" it doesnt work! i do no want it to return anything
additional
back,
for example, as i am just searching for peoples names in the 20
sheets
to
see
if they are present i just want it to return the persons name if
they
are
present, i do not require any additional information being returned.

how can i change the formula to do this?

thanks alot.

"T. Valko" wrote:

Your 2nd formula works but you have mismatched references:

=VLOOKUP(B2,..........,B1)............)

Both of those references need to the same.

Also, don't forget that this is an array formula. Array formulas
need
to
be
entered using the key combination of CTRL,SHIFT,ENTER (not just
ENTER)


--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
Hi, I have the following 2 formulae and i have been desperately
trying
to
make them work but the dont!

I am looking up the value of column D in 1 worksheet to look
across
10
different sheets (which are all named) and then return the
results
as
"1,0"
when done into another sheet. So i am just looking up names from
column
D
of
sheet"names" and then looking across 10 sheets, again in the same
column -
D.

These are the formulae i have tried. the 1st works on the sheets
being
called sheets 1,2 etc and not being renamed and the 2nd formula
allows
you
to
name the sheets then refer to them seperatly. i would like to use
the
2nd
as
i all my sheets are named.

=VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0)



=VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0)


I either get the response N/A or VALUE. i dont know if i have got
all
the
referenes correct, maybe thats why its not working.

maybe the "d2:d100" is incorrect as i repeat it in the formula,
but
this
is
the range where the lookup should look in.

I hope im making sense.

Thanks for your help.