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