search multiple worksheets for an item and return the Wsheets name

[Chris]

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]

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

 

[hgrove]

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(...)).


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 Janec!K5:K20 should all be distinct, but bad things
have a tendency to happen.

 

[Frank Kabel]

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]

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

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

 

[Frank Kabel]

Hi
yes it is

--
Regards
Frank Kabel
Frankfurt, Germany

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

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]

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

Hi
yes it is

--
Regards
Frank Kabel
Frankfurt, Germany

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

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

 

[Frank Kabel]

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

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

Hi
yes it is

--
Regards
Frank Kabel
Frankfurt, Germany

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

:

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]

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

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

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

Hi
yes it is

--
Regards
Frank Kabel
Frankfurt, Germany

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

:

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]

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

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

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

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

:

Hi
yes it is

--
Regards
Frank Kabel
Frankfurt, Germany

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

:

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

 

[Harlan Grove]

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]

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

 

[Harlan Grove]

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.

 

[Chris]

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

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.

 

[Harlan Grove]

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

 

[Chris]

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

 

[Chris]

Nice one it works

Thanks a mill