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

 

[Jay Petrulis]

Are you only checking one row at a time? If not, here is an amendment to my previous offering, which will list all the names having the MAX value.

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
            On Error Resume Next
            If WorksheetFunction.Search(Cells(x, usedcell.Column), answer, 1) = 0 Then
                answer = answer & Cells(x, usedcell.Column) & ", "
            End If
            On Error GoTo 0
        End If
    End If
Next usedcell

MAXVALUE = Left(answer, Len(answer) - 2)

End Function

Note that this will only return one instance of each name. If Dave, for example, twice had the max, Dave would show only once in the result.

 

[Nimrod]

This formula will show any and ALL headings that have the max value.

With my formula I used a1,b1,c1 as headings and a2,b2,c2 as values.

I'm sure there is a prettier way to do this with array formulas but this works.

=IF(A2=(MAX(A2:C2)),A1,"") & " " & IF(B2=(MAX(A2:C2)),B1,"") & " " & IF(C2=(MAX(A2:C2)),C1,"")

 

[Aladin Akyurek]

Jay,

I never looked into this thread because of the topic title -- thinking it was VBA.

See also:

http://www.mrexcel.com/board/viewtopic.php?topic=7334&forum=2&3

Aladin

 

[Jay Petrulis]

Hi Aladin,

I remember that thread! Nice job. Very clever and effective.