search multiple worksheets for an item and return the Wsheets name
 

I have 12 worksheets (Jan.. Dec). I want to enter a unique ref. number and
then search the worksheets in turn Jan.. Dec. If found return the worksheet
name or return 'Not found'. I've tried this
=IF(COUNTIF(January!K5:K20,Q5)0,"January","No match")
Where January is my worksheet name
K5:K20 is the range to look at
Q5 is the cell that I have entered my unique No.

I've also listed my worksheet names in a named range on another sheet but
cannot get this to work either.

Thanks
Chris

Hi
AFAIK not really possible with formulas. This would require VBA to
return the sheet name

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have 12 worksheets (Jan.. Dec). I want to enter a unique ref.
number and
then search the worksheets in turn Jan.. Dec. If found return the
worksheet
name or return 'Not found'. I've tried this
=IF(COUNTIF(January!K5:K20,Q5)0,"January","No match")
Where January is my worksheet name
K5:K20 is the range to look at
Q5 is the cell that I have entered my unique No.

I've also listed my worksheet names in a named range on another sheet
but
cannot get this to work either.

Thanks
Chris

Frank Kabel wrote...
AFAIK not really possible with formulas. This would require VBA to
return the
sheet name
...

You need to read OPs' posts more carefully AND use more imagination.
Specifically, reread the OP's last paragraph, and use
COUNTIF(INDIRECT(...)).

"Chris" schrieb im Newsbeitrag
I have 12 worksheets (Jan.. Dec). I want to enter a unique ref.
number and
then search the worksheets in turn Jan.. Dec. If found return the
worksheet
name or return 'Not found'. I've tried this
=IF(COUNTIF(January!K5:K20,Q5)0,"January","No match")
Where January is my worksheet name
K5:K20 is the range to look at
Q5 is the cell that I have entered my unique No.

I've also listed my worksheet names in a named range on another sheet
but
cannot get this to work either.

If your range of worksheet names is in either a single column or a
single row range named WSLst, try the array formula

=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSLst&"'!K5:K20"),Q5)0)),
INDEX(WSLst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSLst& "'!K5:K20"),Q5)0,0)),
"Not found")

This will give the first match if there are multiple matches. I know
the numbers in Jan:Dec!K5:K20 should all be distinct, but bad things
have a tendency to happen.


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=274450

Hi Harlan
[...]

=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSLst&"'!K5:K20"),Q5)0)),

INDEX(WSLst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSLst& "'!K5:K20"),Q5)0,0))
,
"Not found")

Nice and you're right. Should have thought about something like that
Frank

Sorry to be thick here
what's the WSLst, is this the named range of my worksheet names?

"Frank Kabel" wrote:

Hi Harlan
[...]

=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSLst&"'!K5:K20"),Q5)0)),

INDEX(WSLst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSLst& "'!K5:K20"),Q5)0,0))
,
"Not found")

Nice and you're right. Should have thought about something like that
Frank

Hi
yes it is

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
Sorry to be thick here
what's the WSLst, is this the named range of my worksheet names?

"Frank Kabel" wrote:

Hi Harlan
[...]

=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSLst&"'!K5:K20"),Q5)0)),


INDEX(WSLst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSLst& "'!K5:K20"),Q5)0,0))
,
"Not found")

Nice and you're right. Should have thought about something like
that
Frank

Hi me again
I tried this and get a #n/a error. The WSLst named range in on another
worksheet, maybe I'm addressing it in correctly?
Would it be easier to have the name of the work sheet in a cell ref and then
return that ?

Chris

"Frank Kabel" wrote:

Hi
yes it is

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
Sorry to be thick here
what's the WSLst, is this the named range of my worksheet names?

"Frank Kabel" wrote:

Hi Harlan
[...]

=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSLst&"'!K5:K20"),Q5)0)),


INDEX(WSLst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSLst& "'!K5:K20"),Q5)0,0))
,
"Not found")

Nice and you're right. Should have thought about something like
that
Frank

Hi
just put the list of your sheet names in some cells on your worksheet
(e.g. X1:X10) and use this cell reference then

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
Hi me again
I tried this and get a #n/a error. The WSLst named range in on
another
worksheet, maybe I'm addressing it in correctly?
Would it be easier to have the name of the work sheet in a cell ref
and then
return that ?

Chris

"Frank Kabel" wrote:

Hi
yes it is

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
Sorry to be thick here
what's the WSLst, is this the named range of my worksheet names?

"Frank Kabel" wrote:

Hi Harlan
[...]


=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSLst&"'!K5:K20"),Q5)0)),



INDEX(WSLst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSLst& "'!K5:K20"),Q5)0,0))
,
"Not found")

Nice and you're right. Should have thought about something like
that
Frank

Hi
been playing with this. If I enter the number 1 (one) I get #N/A if I enter
2 then I get Not Found, so it seasms to do a sort of check (one is a valid
entry so it should return january) These references are numerical but are not
sorted and also on the worksheets it checking there will be blanks in the
ranges. Does this make a difference?


Chris

"Frank Kabel" wrote:

Hi
just put the list of your sheet names in some cells on your worksheet
(e.g. X1:X10) and use this cell reference then

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
Hi me again
I tried this and get a #n/a error. The WSLst named range in on
another
worksheet, maybe I'm addressing it in correctly?
Would it be easier to have the name of the work sheet in a cell ref
and then
return that ?

Chris

"Frank Kabel" wrote:

Hi
yes it is

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
Sorry to be thick here
what's the WSLst, is this the named range of my worksheet names?

"Frank Kabel" wrote:

Hi Harlan
[...]


=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSLst&"'!K5:K20"),Q5)0)),



INDEX(WSLst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSLst& "'!K5:K20"),Q5)0,0))
,
"Not found")

Nice and you're right. Should have thought about something like
that
Frank

hi again
looking at the formula it appears to search and find the ref number in
January (which is true) but returns the #N/A rather than the worksheet name
from the WSLst named range

Chris

"Chris" wrote:

Hi
been playing with this. If I enter the number 1 (one) I get #N/A if I enter
2 then I get Not Found, so it seasms to do a sort of check (one is a valid
entry so it should return january) These references are numerical but are not
sorted and also on the worksheets it checking there will be blanks in the
ranges. Does this make a difference?


Chris

"Frank Kabel" wrote:

Hi
just put the list of your sheet names in some cells on your worksheet
(e.g. X1:X10) and use this cell reference then

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
Hi me again
I tried this and get a #n/a error. The WSLst named range in on
another
worksheet, maybe I'm addressing it in correctly?
Would it be easier to have the name of the work sheet in a cell ref
and then
return that ?

Chris

"Frank Kabel" wrote:

Hi
yes it is

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
Sorry to be thick here
what's the WSLst, is this the named range of my worksheet names?

"Frank Kabel" wrote:

Hi Harlan
[...]


=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSLst&"'!K5:K20"),Q5)0)),



INDEX(WSLst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSLst& "'!K5:K20"),Q5)0,0))
,
"Not found")

Nice and you're right. Should have thought about something like
that
Frank

"Chris" wrote...
looking at the formula it appears to search and find the ref number in
January (which is true) but returns the #N/A rather than the worksheet name
from the WSLst named range

Show the *EXACT* formula you're using. I tested the original I proposed,

=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSLst&"'!K5:K20"),Q5)0)),
INDEX(WSLst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSLst& "'!K5:K20"),Q5)0,0)),
"Not found")

and it works, i.e., it never returns #N/A unless there's an #N/A in WSLst
itself. That is, if WSLst referred to SomeWorksheet!A1:A12, and that range
contained Jan, Feb, ..., Dec, respectively in each cell, and each of these
cells contained the name of another worksheet in the same workbook, there's
no way it could return #N/A.

Here you go
=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSlst&"'!K5:K20"),Q5)0)),IN DEX(WSlst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSlst&"' !K5:K20"),Q5)0,0)),"Not Found")



"Harlan Grove" wrote:

"Chris" wrote...
looking at the formula it appears to search and find the ref number in
January (which is true) but returns the #N/A rather than the worksheet name
from the WSLst named range

Show the *EXACT* formula you're using. I tested the original I proposed,

=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSLst&"'!K5:K20"),Q5)0)),
INDEX(WSLst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSLst& "'!K5:K20"),Q5)0,0)),
"Not found")

and it works, i.e., it never returns #N/A unless there's an #N/A in WSLst
itself. That is, if WSLst referred to SomeWorksheet!A1:A12, and that range
contained Jan, Feb, ..., Dec, respectively in each cell, and each of these
cells contained the name of another worksheet in the same workbook, there's
no way it could return #N/A.

"Chris" wrote...
Here you go
=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSlst&"'!K5:K20"),Q5)0)),
INDEX(WSlst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSlst &"'!K5:K20"),Q5)0,0)),
"Not Found")

For this to return #N/A, either the first or second argument to IF would
need to evaluate to #N/A. The only way the first could do so would be if one
of the entries in WSlst itself were #N/A. What's the entire contents of your
WSlst? You should type the formula =WSlst, press [F9] which will evaluate
WSlst, copy the result to the clipboard, and paste into your response. If
there are any #N/A entries in it, that's the problem.

{"January";"February";"March";"April";"May";"June" ;"July";"August";"September";"October";"November"; "December"}
ther you go

"Harlan Grove" wrote:

"Chris" wrote...
Here you go
=IF(SUMPRODUCT(--(COUNTIF(INDIRECT("'"&WSlst&"'!K5:K20"),Q5)0)),
INDEX(WSlst,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSlst &"'!K5:K20"),Q5)0,0)),
"Not Found")

For this to return #N/A, either the first or second argument to IF would
need to evaluate to #N/A. The only way the first could do so would be if one
of the entries in WSlst itself were #N/A. What's the entire contents of your
WSlst? You should type the formula =WSlst, press [F9] which will evaluate
WSlst, copy the result to the clipboard, and paste into your response. If
there are any #N/A entries in it, that's the problem.

Have you entered the formula as an array formula, holding down [Ctrl] and
[Shift] keys before pressing enter?

No I just copied and paste the formula. I'll try this

"Harlan Grove" wrote:

Have you entered the formula as an array formula, holding down [Ctrl] and
[Shift] keys before pressing enter?

Nice one it works

Thanks a mill

"Harlan Grove" wrote:

Have you entered the formula as an array formula, holding down [Ctrl] and
[Shift] keys before pressing enter?