Get value from different excel sheets with criteria

July 25, 2024

i want to get value from another excel sheets based on some criteria. at first look at the picture.

here in the gray highlighted cell i want to get the value from other sheet and it’ll be dynamic and the criteria will be where fiscal year =2019-20. for more clarification look at the second picture.

there could be several sheets named sheet1, sheet1(2),sheet1(3)=,Sheet1(4) and many more. now i want to search all the sheets and get only the Total amount(Marked in F colums) from where Fiscal year:2019-20 for this particular case.

Extra Information: There only one sheet contain Cell Value "Fiscal Year:2019-20" in this workbook.

I am new to excel don’t get the idea of vlookup and indirect function of the excel. Please Solve this issue. thanks.

>Solution :

This works, but I’ve had to guess the address of your list:

Sub test()
    On Error Resume Next
    Dim c As Range, search_for As String
    For Each c In ActiveSheet.Range("B3:B10") ' this is the range with the list of FYs
        search_for = "Fiscal Year: " & c.Value
        c.Offset(, 2).Value = Application.WorksheetFunction _
            .Sum(find_sheet(search_for).Range("F:F"))
    Next
End Sub

Function find_sheet(string_to_search_A1_for) As Worksheet
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        If sh.Cells(1, 1).Value = string_to_search_A1_for Then
            Set find_sheet = sh
            Exit For
        End If
    Next
End Function

When you run test(), it loops through the range B3:B10 and using the value found combined with the prefix forms a string. It then sums column F of the worksheet found with that string in A1 and places it in the cell 2 to the right.

Alternatively, you could use the following UDF to find the sheet, and then use Indirect combined with that to get the value.

Function find_sheet_name(string_to_search_A1_for) As String
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        If sh.Cells(1, 1).Value = string_to_search_A1_for Then
            find_sheet_name = sh.Name
            Exit For
        End If
    Next
End Function

This can be used like so:

=SUM(INDIRECT("'"& Find_sheet_name("Fiscal Year: " & B4) & "'!F:F"))

I can’t think of a way to do this with just a formula and no VBA. But then you did tag VBA in the question..

What you should have done, is name each worksheet with the Fiscal Year it represents. Then this would instead be a very simple formula.

You could use the following to rename the tabs:

Sub Rename_sheets()
    string_to_search_A1_for = "Fiscal Year: "
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        If Left(sh.Cells(1, 1).Value, Len(string_to_search_A1_for)) = string_to_search_A1_for Then
            sh.Name = Mid(sh.Cells(1, 1).Value, Len(string_to_search_A1_for) + 1, 99)
        End If
    Next
End Sub

This would then allow you to use the formula: