Search Entire Workbook

**SpeckM** · 01-07-2015, 10:16 AM

Good Morning,

I'm looking to search my entire workbook for text.
What I would like to do is type a name in cell M4 and then show in M5 how many times that name was found in my workbook.
Can this be done?

**vlady** · 01-07-2015, 10:29 AM

it can be done.
First create a list of the sheet tabs say from A1 to A10 then
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A10&"'!a1:z500"),M4))
a1:z500 is the range to look for.

another approach is to create a named range for the list of sheet tabs

Go to Formulas Menu -> select Name Manager -> NEW - > in the Name box say LISTA -> and in the Refers to box -> Select the range of the tab names that is in A1 to A10

then the formula becomes

=SUMPRODUCT(COUNTIF(INDIRECT("'"&LISTA&"'!a1:z500"),M4))

**sktneer** · 01-07-2015, 10:48 AM

Hi SpeckM,
Welcome to the Forum.

Other than what Vladimir suggested and without having sufficient details, the other option is to have a macro to count in whole workbook.
Just to give you an idea that it is very much possible and depends on what is your actual requirement, please find the workbook and click on the Green button on Sheet1 to get the count of the word Hello in M5 in all the sheets except sheet1.