# Conditional Search Macro for Multiple Worksheets Needed ASAP!

#### Rockstar_Di

##### New Member
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.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

##### Well-known Member
here's a function macro that should do what you ask:

Code:
``````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

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

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

Replies
1
Views
309
Replies
11
Views
1K
Replies
0
Views
1K
Replies
1
Views
647
Replies
7
Views
2K

1,191,361
Messages
5,986,203
Members
440,010
Latest member
cdotshel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back