I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

On Mar 5, 3:09*pm, Opal wrote:
I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. *I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Just a little more information...perhaps there is an easier way
to get to what I want. I have a pivot table that shows downtime
for equipment in the shop. I have it set up to show only the top
3 incidents per week. I need to create a chart from the data
and my boss does not want a pivot chart. In the chart I need
to show not only the data, but the column heading. When
a breakdown occurs, our maintenance group classifies the
problem among several different reason codes which vary by
piece of equipment. These are the column headings. I
thought that if I could put in a formula that tells me the
location of the maximum value in the table I could then
use that info to point me to the correct column heading
to reference on the chart.....?? :-S Any advice would
be appreciated.

On Thu, 5 Mar 2009 12:09:11 -0800 (PST), Opal
wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?


Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:A K9,0)-1,4)

Hope this helps / Lars-Åke

Try

=ADDRESS(9,MATCH(MAX(B9:AK9),B9:AK9)+1)

Mike

"Opal" wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

On Mar 5, 3:23*pm, Lars-Åke Aspelin wrote:
On Thu, 5 Mar 2009 12:09:11 -0800 (PST), Opal
wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. *I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:A K9,0)-1,4)

Hope this helps / Lars-Åke

Thank you, Lars-Ake...it works....

Now, How can I point to the same column location but row 6 to get my
column heading?

On Thu, 5 Mar 2009 12:38:17 -0800 (PST), Opal
wrote:

On Mar 5, 3:23*pm, Lars-Åke Aspelin wrote:
On Thu, 5 Mar 2009 12:09:11 -0800 (PST), Opal
wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. *I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:A K9,0)-1,4)

Hope this helps / Lars-Åke

Thank you, Lars-Ake...it works....

Now, How can I point to the same column location but row 6 to get my
column heading?

To get the data from row 6 in the same column as the maximum value of
the range in row 9, try this:

=INDEX(B6:AK6,MATCH(MAX(B9:AK9),B9:AK9,0))

Hope this helps / Lars-Åke

On Mar 5, 4:18*pm, Lars-Åke Aspelin wrote:
On Thu, 5 Mar 2009 12:38:17 -0800 (PST), Opal
wrote:





On Mar 5, 3:23*pm, Lars-Åke Aspelin wrote:
On Thu, 5 Mar 2009 12:09:11 -0800 (PST), Opal
wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. *I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:A K9,0)-1,4)

Hope this helps / Lars-Åke

Thank you, Lars-Ake...it works....

Now, How can I point to the same column location but row 6 to get my
column heading?

To get the data from row 6 in the same column as the maximum value of
the range in row 9, try this:

=INDEX(B6:AK6,MATCH(MAX(B9:AK9),B9:AK9,0))

Hope this helps */ Lars-Åke- Hide quoted text -

- Show quoted text -

Wow Lars-Ake that's great.....could I ask for one more?

Row 6 were sub headings, I also need the data from row 5... but here's
the kicker, the data in row 5 is not in the same column as row 6
The pivot shows top 3 issues and then sub headings for reason codes
noted by our maintenance people. So I have a part number in row 5
and reason codes in part 6. Some incidents will have 3 reason codes,
some 4, some 5...etc.... never the same for each incident but always 3
part numbers....I am really puzzled as to how to grab this information
and
match it up to my data and reason code.....

On Mar 5, 4:51*pm, Opal wrote:
On Mar 5, 4:18*pm, Lars-Åke Aspelin wrote:





On Thu, 5 Mar 2009 12:38:17 -0800 (PST), Opal
wrote:

On Mar 5, 3:23*pm, Lars-Åke Aspelin wrote:
On Thu, 5 Mar 2009 12:09:11 -0800 (PST), Opal
wrote:

I have been searching for hours with no resolution...

I need to find the cell location, not the value, of the maximum value
in a range. *I am using Excel 2003.

I have tried:

=MATCH(MAX(B9:AK9),B9:AK9)

and the result I get is 15
doesn't help me...the location I need is N9

can anyone help?

Try this formula:

=ADDRESS(ROW(B9),COLUMN(B9)+MATCH(MAX(B9:AK9),B9:A K9,0)-1,4)

Hope this helps / Lars-Åke

Thank you, Lars-Ake...it works....

Now, How can I point to the same column location but row 6 to get my
column heading?

To get the data from row 6 in the same column as the maximum value of
the range in row 9, try this:

=INDEX(B6:AK6,MATCH(MAX(B9:AK9),B9:AK9,0))

Hope this helps */ Lars-Åke- Hide quoted text -

- Show quoted text -

Wow Lars-Ake that's great.....could I ask for one more?

Row 6 were sub headings, I also need the data from row 5... but here's
the kicker, the data in row 5 is not in the same column as row 6
The pivot shows top 3 issues and then sub headings for reason codes
noted by our maintenance people. *So I have a part number in row 5
and reason codes in part 6. *Some incidents will have 3 reason codes,
some 4, some 5...etc.... never the same for each incident but always 3
part numbers....I am really puzzled as to how to grab this information
and
match it up to my data and reason code.....- Hide quoted text -

- Show quoted text -

That's okay....got a work around recording a macro that copies, pastes
(transposes), deletes
blanks and copies to the cells where I need it.... Thanks for all
your help!