CODEWITHSUNDEEP
excelrangenamed
Ramon
Ramon

Reputation: 3

Excel - Retrieve cell name and range name

When I look for min/max, not only I would like the result, but also the cell name the result is in as well as it's range name.

e.g. =MAX(B2:C20)

The max is 100 in B6, B6 is part of a named range called Week 1.

Therefore we would like to have a formula that can retrieve:

100, B6, WEEK 1

Please help, I'be been up all night looking for this.

Many thanks.

Upvotes: 1

Views: 83

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

In this case I defined the range B5:B7 to have the Name WEEK1:

enter image description here

and this short UDF():

Public Function FindMax(rIN As Range) As String
    Dim mx As Variant, rF As Range, N As Name
    mx = Application.WorksheetFunction.Max(rIN)
    Set rF = rIN.Find(What:=mx, After:=rIN(1))

    For Each N In ThisWorkbook.Names
        If Not Intersect(rF, Range(N.RefersTo)) Is Nothing Then
            lab = N.Name
        End If
    Next N

    FindMax = mx & ", " & rF.Address(0, 0) & ", " & lab
End Function

returns:

enter image description here

You must be carefully to exclude print ranges, etc.

EDIT#1:

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=FindMax(A1:Z100)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Upvotes: 1

Related Questions