showing cell locations per answer from Ron Coddere - stuck!

[Mea]

I am trying to track 3 things, with the info from a linked file.
1. If the "name" I'm looking for is in the reference file. I'm using
=IF(COUNTIF('2009_Maps(1).xls'!southbed,B2)>0,"south", " ")

2.The number of occurences of the name. I'm using
=COUNTIF('2009_Maps(1).xls'!southbed,B2)
Both these formula's work, provided that the name matches exactly. For
example, this will find "cadmium" but not "cadmium sp". How can I check for
cells that contain the partial name, as well as the full name?

3. I want to show the cell address for each occurence.
I can across a post from Ron that gave me this formula to find a cell
location.
=ADDRESS(SUMPRODUCT((A1:E10=G1)*ROW(A1:A10)),SUMPRODUCT((A1:E10=G1)*COLUMN(A1:E1)))

I have am using a range, and want nothing in the field if there are no
occurences, so I have used this.
=IFERROR(ADDRESS(SUMPRODUCT(('2009_Maps(1).xls'!southbed=B4)*ROW('2009_Maps(1).xls'!southbed)),SUMPRODUCT(('2009_Maps(1).xls'!southbed=B4)*COLUMN('2009_Maps(1).xls'!southbed)))," ")
However, this only works when there is one location. How can I have cell
address' for ALL locations show up. And have the answer, $CZ$613, show as
CZ613.

I'm in over my head, and the head of my resident Excel guru!, so I'm hoping
someone can help

[Pete_UK]

To answer your first question, you can use wildcards with COUNTIF, so
you can change the B2 reference to B2&"*" in both formulae.

Hope this helps.

Pete

On Aug 7, 4:28 am, Mea <M...@discussions.microsoft.com> wrote:
> I am trying to track 3 things, with the info from a linked file.
> 1. If the "name" I'm looking for is in the reference file. I'm using
> =IF(COUNTIF('2009_Maps(1).xls'!southbed,B2)>0,"south", " ")
>
> 2.The number of occurences of the name. I'm using
> =COUNTIF('2009_Maps(1).xls'!southbed,B2)
> Both these formula's work, provided that the name matches exactly. For
> example, this will find "cadmium" but not "cadmium sp". How can I check for
> cells that contain the partial name, as well as the full name?
>
> 3. I want to show the cell address for each occurence.
> I can across a post from Ron that gave me this formula to find a cell
> location.

> =ADDRESS(SUMPRODUCT((A1:E10=G1)*ROW(A1:A10)),SUMPRODUCT((A1:E10=G1)*COLUMN(­A1:E1)))

>
> I have am using a range, and want nothing in the field if there are no
> occurences, so I have used this.

> =IFERROR(ADDRESS(SUMPRODUCT(('2009_Maps(1).xls'!southbed=B4)*ROW('2009_Maps­(1).xls'!southbed)),SUMPRODUCT(('2009_Maps(1).xls'!southbed=B4)*COLUMN('200­9_Maps(1).xls'!southbed)))," ")