查找包含特定单元格最小值的工作表

I use this:

我用这个:

=MIN(First:Last!H46)

The result is the smallest-value H46 across all my sheets

结果是我所有床单上的最小值H46

I need to find which sheet it's on * (i.e. '05')*

我需要找到它所在的纸张*(即'05')*

I need to use this second result to find other data on that sheet * (i.e. Cell H45 on sheet '05' only)*

我需要使用第二个结果来查找该表*上的其他数据*(即仅在表'05'上的单元格H45)*

My sheetnames are First and Last with the days of the month in between, e.g. 01, 02, 03

我的工作表名称是First和Last,其中包含介于两者之间的日期,例如01,02,03

1 个解决方案

#1

Try this small User Defined Function:

试试这个小的用户定义函数:

Public Function WhereIsMin(s1 As String, s2 As String, r As Range) As String
    Application.Volatile
    Dim StartLooking As Boolean, sh As Worksheet, fformula As String
    Dim findit As Variant, addy As String
    addy = r.Address(0, 0)
    fformula = "Min(" & s1 & ":" & s2 & "!" & addy & ")"
    findit = Evaluate(fformula)
    StartLooking = False
    For Each sh In Worksheets
        If sh.Name = s1 Then StartLooking = True
        If sh.Name = s2 Then StartLooking = False
        If StartLooking Then
                If sh.Range(addy).Value = findit Then
                    WhereIsMin = sh.Name
                    Exit Function
                End If
        End If
    Next sh
End Function

In the worksheet, you would use it like:

在工作表中,您将使用它:

=WhereIsMin("Sheet2","Sheet4",B6)

#1