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.

For a VLOOKUP to function with an offset of more than 1, the LookupRange must
include the columns that contain the data to be returned as well as the
column that contains the value to be looked up.........

=VLOOKUP(A1,A:A,1,FALSE) will work...
=VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE)

Vaya con Dios,
Chuck, CABGx3



"Gemz" wrote:

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.

But i am looking in a number of sheets, how can i tell this formula to lookup
c:c in each sheet. the thing is because i am only looking up names i just
need to see if the name on one sheet is present anywhere within the 20 sheets
and it doesnt need to return anything other than "1,false" which means just
to return the name IF it appears anywhere within the 20 sheets.

thanks.

"CLR" wrote:

For a VLOOKUP to function with an offset of more than 1, the LookupRange must
include the columns that contain the data to be returned as well as the
column that contains the value to be looked up.........

=VLOOKUP(A1,A:A,1,FALSE) will work...
=VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE)

Vaya con Dios,
Chuck, CABGx3



"Gemz" wrote:

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.

Sorry, I guess I missed your point. I've never done exactly what you are
attempting, but I think my first choice would be VBA.....and my second would
be to use a helper column with the 20 individual VLOOKUPS there and then use
one master VLOOKUP to check that column for results...........just my
thoughts...

Vaya con Dios,
Chuck, CABGx3




"Gemz" wrote:

But i am looking in a number of sheets, how can i tell this formula to lookup
c:c in each sheet. the thing is because i am only looking up names i just
need to see if the name on one sheet is present anywhere within the 20 sheets
and it doesnt need to return anything other than "1,false" which means just
to return the name IF it appears anywhere within the 20 sheets.

thanks.

"CLR" wrote:

For a VLOOKUP to function with an offset of more than 1, the LookupRange must
include the columns that contain the data to be returned as well as the
column that contains the value to be looked up.........

=VLOOKUP(A1,A:A,1,FALSE) will work...
=VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE)

Vaya con Dios,
Chuck, CABGx3



"Gemz" wrote:

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.

Maybe you overlooked the formulas that i have been playing around with..


=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 got these off previous posts on this website, where people have been
provided formule to work through, i tried to apply it to my situation but it
didnt work.

if not via formula, how do i do it via vba?

please help, i really dont know what to do.

"CLR" wrote:

Sorry, I guess I missed your point. I've never done exactly what you are
attempting, but I think my first choice would be VBA.....and my second would
be to use a helper column with the 20 individual VLOOKUPS there and then use
one master VLOOKUP to check that column for results...........just my
thoughts...

Vaya con Dios,
Chuck, CABGx3




"Gemz" wrote:

But i am looking in a number of sheets, how can i tell this formula to lookup
c:c in each sheet. the thing is because i am only looking up names i just
need to see if the name on one sheet is present anywhere within the 20 sheets
and it doesnt need to return anything other than "1,false" which means just
to return the name IF it appears anywhere within the 20 sheets.

thanks.

"CLR" wrote:

For a VLOOKUP to function with an offset of more than 1, the LookupRange must
include the columns that contain the data to be returned as well as the
column that contains the value to be looked up.........

=VLOOKUP(A1,A:A,1,FALSE) will work...
=VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE)

Vaya con Dios,
Chuck, CABGx3



"Gemz" wrote:

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.

This may get you started.........

Sub FindTheName()
Dim sh As Worksheet
Dim searchname
Dim k
Dim iCount As Integer
iCount = 0
k = InputBox("Enter NAME to search for:") 'case sensitive
searchname = k
For Each sh In Worksheets
sh.Select
Dim lastrow As Long, r As Long
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For r = lastrow To 1 Step -1
If Cells(r, "D") = k Then
MsgBox "Name found on " & ActiveSheet.Name & " in cell " & Cells(r,
"d").Address '"tom"
iCount = iCount + 1
Else
End If
Next r
Next sh
If iCount = 0 Then
MsgBox "NAME NOT FOUND"
Else
MsgBox "NAME FOUND " & iCount & " TIMES"
End If
End Sub


Vaya con Dios,
Chuck, CABGx3


"Gemz" wrote:

Maybe you overlooked the formulas that i have been playing around with..


=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 got these off previous posts on this website, where people have been
provided formule to work through, i tried to apply it to my situation but it
didnt work.

if not via formula, how do i do it via vba?

please help, i really dont know what to do.

"CLR" wrote:

Sorry, I guess I missed your point. I've never done exactly what you are
attempting, but I think my first choice would be VBA.....and my second would
be to use a helper column with the 20 individual VLOOKUPS there and then use
one master VLOOKUP to check that column for results...........just my
thoughts...

Vaya con Dios,
Chuck, CABGx3




"Gemz" wrote:

But i am looking in a number of sheets, how can i tell this formula to lookup
c:c in each sheet. the thing is because i am only looking up names i just
need to see if the name on one sheet is present anywhere within the 20 sheets
and it doesnt need to return anything other than "1,false" which means just
to return the name IF it appears anywhere within the 20 sheets.

thanks.

"CLR" wrote:

For a VLOOKUP to function with an offset of more than 1, the LookupRange must
include the columns that contain the data to be returned as well as the
column that contains the value to be looked up.........

=VLOOKUP(A1,A:A,1,FALSE) will work...
=VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE)

Vaya con Dios,
Chuck, CABGx3



"Gemz" wrote:

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.

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.

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.

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.

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.

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.

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.

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.

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.

I have posted the below in the programming forum but no reply as of yet.

Meanwhile, I have another question regarding the 'worksheet function' side:

The formula below allows me to lookup 1 colmun in sheet 1 into column E in
20 different sheets.

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

I now need to look the other way around i.e. whats in the 20 sheets that
isnt in sheet 1. so, instead of 'lookup B2 in WSList' (which is my sheet
range).. i need to lookup WSList into B2. i tried altering the formula abit
but it didnt work.

how do i do this?

thanks a lot.

"T. Valko" wrote:

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.

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.

how do i do this?

Can't be done with a single formula.

It sounds like you should consider hiring a consultant! <g


--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
I have posted the below in the programming forum but no reply as of yet.

Meanwhile, I have another question regarding the 'worksheet function'
side:

The formula below allows me to lookup 1 colmun in sheet 1 into column E in
20 different sheets.

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

I now need to look the other way around i.e. whats in the 20 sheets that
isnt in sheet 1. so, instead of 'lookup B2 in WSList' (which is my sheet
range).. i need to lookup WSList into B2. i tried altering the formula
abit
but it didnt work.

how do i do this?

thanks a lot.

"T. Valko" wrote:

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.

normally i used to get replies from these forums in
minutes but now its coming to days,
is there any reason for this?

It's probably due to the complexity of your request. The more complex the
problem, the fewer people there are that are qualified to solve it and less
of a chance that one of these people will see your request. And then, how
many of those are willing to spend the time to solve the problem (for free).

--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
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.

Hi,

Are you not able to offer a bit of guidance as to how i can get around this?
how many formulas and what type?

i really appreciate your help.

"T. Valko" wrote:

how do i do this?

Can't be done with a single formula.

It sounds like you should consider hiring a consultant! <g


--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
I have posted the below in the programming forum but no reply as of yet.

Meanwhile, I have another question regarding the 'worksheet function'
side:

The formula below allows me to lookup 1 colmun in sheet 1 into column E in
20 different sheets.

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

I now need to look the other way around i.e. whats in the 20 sheets that
isnt in sheet 1. so, instead of 'lookup B2 in WSList' (which is my sheet
range).. i need to lookup WSList into B2. i tried altering the formula
abit
but it didnt work.

how do i do this?

thanks a lot.

"T. Valko" wrote:

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.

You need to have formulas on each of the 20 sheets, 1 per lookup_value, that
does a lookup back to the sheet1.

You can only do so much with formulas. What you want to do isn't possible
with a single formula. If you don't want to go the route I've outlined above
then your only other choice is a VBA coded procedure. This procedure would
not be a trivial matter and this relates to my other reply about why you
might not be getting "quick" responses. The more complex the request, the
less chance of getting a "free" solution.

Think of it this way: I'm looking for volunteers to either change the
transmission in my car or wash the windshield. Which task is more likely to
get volunteers?

--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
Hi,

Are you not able to offer a bit of guidance as to how i can get around
this?
how many formulas and what type?

i really appreciate your help.

"T. Valko" wrote:

how do i do this?

Can't be done with a single formula.

It sounds like you should consider hiring a consultant! <g


--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
I have posted the below in the programming forum but no reply as of yet.

Meanwhile, I have another question regarding the 'worksheet function'
side:

The formula below allows me to lookup 1 colmun in sheet 1 into column E
in
20 different sheets.

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

I now need to look the other way around i.e. whats in the 20 sheets
that
isnt in sheet 1. so, instead of 'lookup B2 in WSList' (which is my
sheet
range).. i need to lookup WSList into B2. i tried altering the formula
abit
but it didnt work.

how do i do this?

thanks a lot.

"T. Valko" wrote:

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.

thanks for advise and info, so you think it might be worth me making a
request to get something done like this within VBA..coz i dont really want to
do numerous formulae.

I appreciate that easier tasks will get a quicker response but i'll still try.

thanks again for all your help.

"T. Valko" wrote:

You need to have formulas on each of the 20 sheets, 1 per lookup_value, that
does a lookup back to the sheet1.

You can only do so much with formulas. What you want to do isn't possible
with a single formula. If you don't want to go the route I've outlined above
then your only other choice is a VBA coded procedure. This procedure would
not be a trivial matter and this relates to my other reply about why you
might not be getting "quick" responses. The more complex the request, the
less chance of getting a "free" solution.

Think of it this way: I'm looking for volunteers to either change the
transmission in my car or wash the windshield. Which task is more likely to
get volunteers?

--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
Hi,

Are you not able to offer a bit of guidance as to how i can get around
this?
how many formulas and what type?

i really appreciate your help.

"T. Valko" wrote:

how do i do this?

Can't be done with a single formula.

It sounds like you should consider hiring a consultant! <g


--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
I have posted the below in the programming forum but no reply as of yet.

Meanwhile, I have another question regarding the 'worksheet function'
side:

The formula below allows me to lookup 1 colmun in sheet 1 into column E
in
20 different sheets.

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

I now need to look the other way around i.e. whats in the 20 sheets
that
isnt in sheet 1. so, instead of 'lookup B2 in WSList' (which is my
sheet
range).. i need to lookup WSList into B2. i tried altering the formula
abit
but it didnt work.

how do i do this?

thanks a lot.

"T. Valko" wrote:

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