Help Displaying Values and Linking

GourdoftheFries · #1 01-02-2019, 03:28 PM

Howdy folks,

I was using the MAX function to find the highest value for a certain set of numbers in Column F. My function is =MAX(F320:F465)

How do I:
A - Display the cell's # so that it is easy to locate the MAX figure returned by the function within the sheet?
B - Display a cell's value that is in Column A, but in the same row as the MAX function result?
(Column A is the date for the value returned in the MAX function in column F)

Thank you for your time folks,

M

Logit · #2 01-02-2019, 09:07 PM

.
If you don't mind using a macro approach :

Code:

Option Explicit

Private Sub CommandButton1_Click()

Dim maximum As Double, rng As Range, cell As Range

Cells.Interior.ColorIndex = 0

Set rng = Range("F1").CurrentRegion
maximum = WorksheetFunction.Max(rng)
    
For Each cell In rng
    If cell.Value = maximum Then
        cell.Interior.ColorIndex = 22
        Range("G1").Value = cell.Address
    End If
Next cell
    
End Sub

xor · #3 01-03-2019, 01:19 AM

A formula solution:

A: =ADDRESS(MATCH(MAX($F$320:$F$465),$F$320:$F$465,0) +CELL("row",$F$320:$F$465)-1,6)

B: =ADDRESS(MATCH(MAX($F$320:$F$465),$F$320:$F$465,0) +CELL("row",$F$320:$F$465)-1,1)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Table with values from several locations and years - need to find comp values	Ricardo Sousa	Excel	9	07-27-2018 02:06 AM
Table with values from several locations and years - need to find comp values	Ricardo Sousa	Excel	6	06-09-2018 10:51 PM
Multiple found values from five sheets, multiple returned values in sheet six?	irisha	Excel Programming	26	09-30-2016 01:20 AM
Linking fields to pull values from one field to another	KWH	Word Tables	3	01-27-2016 02:25 PM
Display multiple values as new values based on selection from template.	MvdB	Excel	2	09-29-2015 08:51 PM