showing cell locations per answer from Ron Coddere - stuck!

  • Thread starter Thread starter Mea
  • Start date Start date
M

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
 
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
 
Back
Top