I posted on the programming forum but no response and i am in urgent need of
help! normally i used to get replies from these forums in minutes but now its
coming to days, is there any reason for this?

hope you can respond asap.

thanks.

"Gemz" wrote:

Its worked fine now, i dont know why it chooses when to work and when not to
work! Just for future reference, what do you mean "is the worksheet list
static or dynamic"?
Also, its not days of the month.. its a list of job roles across the
organisation so follow no sequential order.

thanks.

"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.