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,"sou th", " ")

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)),SUMPR ODUCT((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'!so uthbed=B4)*ROW('2009_Maps(1).xls'!southbed)),SUMPR ODUCT(('2009_Maps(1).xls'!southbed=B4)*COLUMN('200 9_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

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 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,"sou th", " ")

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)),SUMPR ODUCT((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'!so uthbed=B4)*ROW('2009_Maps*(1).xls'!southbed)),SUMP RODUCT(('2009_Maps(1).xls'!southbed=B4)*COLUMN('20 0*9_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