Use Function to find total number of times a word is used on multiple worksheets.......

[rufiotgl]

.....within a workbook.

Hi All. My name is Dan, and im a bit of an Excel novice, so apologies if this question comes across as a bit stupid or is more simple to do than im making it. Anyway, il attempt to explain the problem as best as possible in the hope that someone can help me.

Situation
I work in IT and have just recently run an audit of a new company that we have just acquired. Now, what i want to do is to break down all the information that i have across 30 sheets within a workbook and put that on a coverpage that will give a quick breakdown as to what is currently present.

So, for examples sake, they have many various PC's and i want to get a list of how many of which machines we have. Now to make it easy, i have picked Dell OptiPlex 760. Now i can get my Function to find "* OptiPlex 760) in Column A across all my sheets, but i want it to be able to search all Rows and All Columns (or at least rows 1-50, and columns A-Z).

This is my formula

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!$A$1:$A$10"), {"*OptiPlex 760"}))

Its probably something really easy, but im exhausted thinking about it and i have nowhere else to go.

As i say, its finding "OptiPlex 760" in column A across all sheets (coz i used column A just as a Test, if you look @ sheet2, you will see its in L31, and on sheet31!! But this varies from sheet to sheet, hence why i need it to search the entire sheet), but i need it to really be able to search ALL COLUMNS and ALL ROWS (or at least rows 1-50, and columns A-Z).

I hope this makes sense?? Iv also uploaded my sheet so you can look at it. Really hope someone can help.

Thanks for your time.

Dan

[daddylonglegs]

Hello Dan,

You can expand the range to any range you want so, for example,

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!$A$1:$Z$50"), "*OptiPlex 760"))

[but note that in your example the formula is in one of the sheets so you get a circular reference - on a summary sheet it will be OK]

[AB33]

Dan,
It can be done by a macro code, but are you comfortable with running a code? Assuming you are, what do you want to search for. If a search is found, where do you want to put the output? Which rows/columns do you want to return, or do you just want to return a single cell?

[rufiotgl]

AB33, daddylonglegs, thanks for your responses.

AB33 - Yeah im familiar with Macros though its been awhile since i created/recorded one. What im looking for is for the Marco/VBA code/Function to search ALL the rows/colums in all my sheets that i add to the workbook to find OpitPlex 760 (and any other keywords/values that i may require)!!

daddylonglegs (great handle BTW) - You was right about the "circular reference" thing, although, i cant say i understand it much, but i did some googling and found a very helpful video (http://www.youtube.com/watch?v=aEhu7bys1Po) which explained it rather well, so im kind of ok now. I think i just needed you to add th "search all columns" part, and then what i can do, is enable "iterative calculation" part and change the search criteria to match the exact text im looking for "Dell Inc. OptiPlex 780" and just label my contents sheet a different name with the Dell column "Dell OptiPlex 760" & because its missing the "Inc." bit, it wont record it as a result.

Hopefully that will give me a satisfactory end result.

Il keep you posted. But yet again, thanks both for your help. Muchos appreciated .

Dan

[AB33]

Dan,
You have not answered my question. When the code searches for OpitPlex 760, where do you want to copy the find row/column? Do you also want a single cell return? In other words, when the code finds the item, do you just want that single cell, not multiple cells?

[rufiotgl]

Thanks AB33

Iv managed to sort it out and im more than happy with the final result.

Cheers

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.