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
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('2009_Maps(1).xls'!southbed)))," ")