Conditional Search Macro for Multiple Worksheets Needed ASAP!

[Rockstar_Di]

Here is what I need to do:

On my Master sheet, I am searching for some data within the entire workbook to bring back and view on the Master sheet.

If the G5 text of my Master sheet equals A1 text of Any sheet within the workbook, I need the Q20 cell (from the sheet it found the Master!G5 info on) to be shown in J5 on the Master sheet as a sum.

I created an Index tab to list all of the sheet names, if that helps.

Maybe this will explain it:
If cell A5 on sheet B equals cell C1 on "sheet D", then I want cell E3 from "sheet D" to equal cell F7 on sheet B.
Sheet B is the "Master" and "sheet D" could be any sheet from within the workbook.
There could be multiple "sheet D"s, if so, all of the "sheet D" E3 cells should be added together.

 

[btadams]

here's a function macro that should do what you ask:

Function SumSheets(strFind As String) As Double
Dim dblVal As Double

    On Error Resume Next
    strFind = Range("G5").Value
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name <> ActiveSheet.Name Then
            If Worksheets(i).Visible = True Then
                If Worksheets(i).Range("A1").Value = strFind Then
                    dblVal = dblVal + Worksheets(i).Range("Q20").Value
                End If
            End If
        End If
    Next i
    SumSheets = dblVal
End Function

 

[Rockstar_Di]

But then how do I get that to go into the cell J5 on the Master sheet?

 

[btadams]

Copy/paste the macro into a standard module in the workbook. Then in cell J5 enter the formula =SumSheets(G5)