Find Min Value, Return Sheet Name

Microsoft Office excel 2007 home & stude...
July 7, 2010 at 12:47:08
Specs: Windows Vista
Question: I have a spread sheet with a large number of worksheets and I want to compare all of the values in cell A2 across all worksheets and have it list the sheet name and the value of the lowest A2 cell….what’s the easiest way to do this?

See More: Find Min Value, Return Sheet Name

Report •

#1
July 7, 2010 at 14:20:49
Are your worksheets in an ordered format? Eg. sheet1, sheet2, sheet3?

Report •

#2
July 7, 2010 at 14:50:39
Yes, each of the worksheets will be individually numbered as follows 1-ABC, 2-XYZ, 3-EFG, 4-UVW etc...

Report •

#3
July 7, 2010 at 18:30:20
You could do it with a User Defined Function (UDF)

Place this code in a Standard Module and enter =MinBySheet() in any cell (except A2 of course).

The UDF starts by assuming that Sheet1!A2 contains the lowest value. It then compares Sheet2!A2 to Sheet1!A2 and if it's lower, it reassigns the nxtShtName and nxtVal variables to the name of Sheet2 and the value in Sheet2!A2.

It then does the same thing for all sheets, updating the variables when it finds a lower value. Once it goes through all the sheets, it returns whatever Sheet.Name and A2 value that are currently stored in those variables.


Option Explicit
Function MinBySheet()
Application.Volatile
Dim nxtVal As Variant
Dim nxtShtName As String
Dim nxtSht As Integer
  nxtVal = Sheets(1).Range("A2")
  nxtShtName = Sheets(1).Name
   For nxtSht = 2 To Sheets.Count
    If Sheets(nxtSht).Range("A2") < nxtVal Then
     nxtVal = Sheets(nxtSht).Range("A2")
     nxtShtName = Sheets(nxtSht).Name
    End If
   Next
  MinBySheet = nxtShtName & "!A2 = " & nxtVal
End Function


Report •

Related Solutions

#4
July 7, 2010 at 19:14:14
BTW...If there are two or more A2's with the "lowest" value, the UDF will return the value and sheet name of the "first" sheet with that value.

If you want to deal with ties in some other manner, let us know what you want to happen and we'll see what we can do.


Report •

#5
July 8, 2010 at 05:42:52
I think were on the right track: I plan on using this function in multiple cells, so can we pass in the A2 value instead of hard coding it in the UDF. Also, will in work multiple times? if I use it for cells A2-A20 thats 18 uses on a summary sheet, seems like I would need 18 result variables. how would that work?

Report •

#6
July 8, 2010 at 06:10:48
One other thing....this is a comparison of the same cell across all sheets in the workbook (except the summary sheet which will be displaying the results and other things). Some sheets may contain "no value" in the cell ... I wouldn't want that sheet to return as the lowest value since it has no value.

Report •

Ask Question