how do u reproduce a column heading elsewhere?

beastwood

New Member
Joined
May 1, 2002
Messages
16
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
=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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top