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