Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to find text and count it within an entire workbook
I have a workbook where there is nothing consistent or any named ranges. I
need to make a summary sheet and count everytime I have the word cable in the entire workbook in column B. I have tried different variations of countif, lookups and find but have not come up with a combination that works. My restraint is that I can't have a macro for this workbook. I really need some help, example below. A B 1 Cable 2 FSR 3 FRK 4 Wind Meter 5 Laptop Thanks in advance, Carrie -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to find text and count it within an entire workbook
Hi,
=COUNTIF($a$1:$H$5000,"*Cable*") if the data is in a different sheet called Data use =COUNTIF(Data!$a$1:$H$5000,"*Cable*") change range to fit your needs "Carrie_Loos via OfficeKB.com" wrote: I have a workbook where there is nothing consistent or any named ranges. I need to make a summary sheet and count everytime I have the word cable in the entire workbook in column B. I have tried different variations of countif, lookups and find but have not come up with a combination that works. My restraint is that I can't have a macro for this workbook. I really need some help, example below. A B 1 Cable 2 FSR 3 FRK 4 Wind Meter 5 Laptop Thanks in advance, Carrie -- Message posted via http://www.officekb.com . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to find text and count it within an entire workbook
Hi Eduardo, thanks but I want to collect this information throughout the
entire workbook, there are several worksheets and none of them are named consistantly. The formula would be unmanageable and after a few changes unrelyable tryining to insert a worksheet name into the formula. Eduardo wrote: Hi, =COUNTIF($a$1:$H$5000,"*Cable*") if the data is in a different sheet called Data use =COUNTIF(Data!$a$1:$H$5000,"*Cable*") change range to fit your needs I have a workbook where there is nothing consistent or any named ranges. I need to make a summary sheet and count everytime I have the word cable in the [quoted text clipped - 12 lines] Thanks in advance, Carrie -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201004/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to find text and count it within an entire workbook
I'm sure there's a way to do it with VBA. I have to think about it. If you
want a quick solution do the following: Ctrl + F enter 'Cable' into Find What click Options Within Workbook Find All. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Carrie_Loos via OfficeKB.com" wrote: Hi Eduardo, thanks but I want to collect this information throughout the entire workbook, there are several worksheets and none of them are named consistantly. The formula would be unmanageable and after a few changes unrelyable tryining to insert a worksheet name into the formula. Eduardo wrote: Hi, =COUNTIF($a$1:$H$5000,"*Cable*") if the data is in a different sheet called Data use =COUNTIF(Data!$a$1:$H$5000,"*Cable*") change range to fit your needs I have a workbook where there is nothing consistent or any named ranges. I need to make a summary sheet and count everytime I have the word cable in the [quoted text clipped - 12 lines] Thanks in advance, Carrie -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201004/1 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to find text and count it within an entire workbook
Don't forget to group select sheets that you want to find an expression in -
otherwise will only search in the active sheet. I note that you want to count the occassions that cable appears -ultimately I believe you should be standardising the input across all sheets then using data validation limiting future entry to items on a lookup list. Note that the list can be on its own sheet and can be referred to as long as it has had a range name applied to it -- Ken "Using Dbase dialects since 82" "Started with Visicalc in the same year" "ryguy7272" wrote: I'm sure there's a way to do it with VBA. I have to think about it. If you want a quick solution do the following: Ctrl + F enter 'Cable' into Find What click Options Within Workbook Find All. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Carrie_Loos via OfficeKB.com" wrote: Hi Eduardo, thanks but I want to collect this information throughout the entire workbook, there are several worksheets and none of them are named consistantly. The formula would be unmanageable and after a few changes unrelyable tryining to insert a worksheet name into the formula. Eduardo wrote: Hi, =COUNTIF($a$1:$H$5000,"*Cable*") if the data is in a different sheet called Data use =COUNTIF(Data!$a$1:$H$5000,"*Cable*") change range to fit your needs I have a workbook where there is nothing consistent or any named ranges. I need to make a summary sheet and count everytime I have the word cable in the [quoted text clipped - 12 lines] Thanks in advance, Carrie -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201004/1 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicates in entire workbook | Excel Discussion (Misc queries) | |||
Find duplicates in entire workbook | Excel Discussion (Misc queries) | |||
Using Find over entire workbook | Excel Programming | |||
How do I count how often a text or number appears in entire worksh | Excel Discussion (Misc queries) | |||
count text strings in an entire workbook | Excel Worksheet Functions |