how do u reproduce a column heading elsewhere?

[beastwood]

problem....

say u have 3 columns named dave john neal

with user inputted numeric entries below each name..is there a way to display either dave john or neal in a seperate cell depending on who has the highest numeric value inputted in the cells below each name?

[ This Message was edited by: beastwood on 2002-05-03 13:53 ]

[zacemmel]

Are John, Dave, Neal the column headers for your numbers? Like, A1=John, B1=Dave, C1= Nail and the numbers are in the rows below? Also, do you want the column header for the column that contains the highest number?

[beastwood]

aye to part one
so if for eg
dave(A1) had 10 in A2
john (B1) had 4 in B2
neal (c1) had 9 in C2

is there a function to display the highest
in this case 10 as dave in say another cell
like D10

[beastwood]

anyone got any ideas?

[sen_edp]

hello

WE HAVE THIS TABLE

A B C D
1 DAVE JOHN NEAL
2 10 2 9

AT D2 WE PUT THE FOLLOWING FORMULA
=INDEX(A1:C1,MATCH((MAX(A2:C2)),A2:C2,0))
AND WE HAVE DAVE

THAT'S ALL

REGARDS

ANDREAS

[Jay Petrulis]

Hi,

If there are multiple rows for your data, try the following UDF

Function MAXVALUE(Rng As Range)
Dim maxval As Double, usedcell As Range
Dim answer As String, x As Long

x = Rng.Row
maxval = WorksheetFunction.Max(Rng)
For Each usedcell In Rng
    If IsNumeric(usedcell) Then
        If usedcell = maxval Then
            answer = usedcell.Address(False, False)
            Exit For
        End If
    End If
Next usedcell

MAXVALUE = Cells(x, Range(answer).Column)

End Function

Call as
=MAXVALUE(A1:C25) for instance.

This assumes that the names are at the top of the range in a row.

[beastwood]

nice one fella

[beastwood]

thx for that

do u know if its also possible to display more than one result eg.if dave and john both are equal highest
both dave and john are displayed

currently only one is displayed

[Yogi Anand]

On 2002-05-05 10:32, beastwood wrote:
thx for that

do u know if its also possible to display more than one result eg.if dave and john both are equal highest
both dave and john are displayed

currently only one is displayed

Try the following formula

=IF(A2=MAX($A$2:$C$2),OFFSET(A2,-1,0),"")

say in cell A11, and then drag to B11, and C11 -- the formula will result in

Dave in cell A11
John in cell B11
and a blank in cell C11

Hope This Helps!

Regards!

Yogi Anand


[ This Message was edited by: Yogi Anand on 2002-05-05 12:06 ]

[beastwood]

=INDEX(C7:G7,MATCH((MAX(C18:G18)),C18:G18,0))

the above is my formula..as u can see there are 5 possible results but it only displays one at a time..im trying tog et it to display 2 or more eg if both c8 and c9 are the highest..

[ This Message was edited by: beastwood on 2002-05-05 12:15 ]