Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: how do u reproduce a column heading elsewhere?

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  4. #4
    New Member
    Join Date
    May 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    anyone got any ideas?

  5. #5
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

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

    Code:
    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.

  7. #7
    New Member
    Join Date
    May 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    nice one fella


  8. #8
    New Member
    Join Date
    May 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    New Member
    Join Date
    May 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •