Prev: sort range
Next: Insert formula even cells with data below it
From: Carrie_Loos via OfficeKB.com on 5 Apr 2010 12:58
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

From: Eduardo on 5 Apr 2010 14:30
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
>
> .
>
From: Carrie_Loos via OfficeKB.com on 5 Apr 2010 14:41
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.aspx/excel-programming/201004/1

From: ryguy7272 on 5 Apr 2010 16:37
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.aspx/excel-programming/201004/1
>
> .
>
From: K_Macd kmacdonald "A_T" activ8 ''''D O T'''' net [ST OP] on 5 Apr 2010 23:33
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.aspx/excel-programming/201004/1
> >
> > .
> >
 | 
Pages: 1
Prev: sort range
Next: Insert formula even cells with data below it