Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-02-2019, 03:28 PM
GourdoftheFries GourdoftheFries is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2019
Posts: 1
GourdoftheFries is on a distinguished road
Default Help Displaying Values and Linking

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
Reply With Quote
  #2  
Old 01-02-2019, 09:07 PM
Logit Logit is offline Windows 10 Office 2007
Competent Performer
 
Join Date: Jan 2017
Posts: 239
Logit is on a distinguished road
Default

.
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
Reply With Quote
  #3  
Old 01-03-2019, 01:19 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 987
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

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)
Reply With Quote
Reply

Tags
formatting, help a novice

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 07:23 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft