software

i have a workbook that i add workshhets to daily, one worksheet for
everyday. i would like to be able to have a reporting page that would
go out to all of the pages included in the workbook and look at 1
column (the same column on all pages) and come back with a count for
cells that have a particular text string.

*running total*
i.e. page '12jan' and page '13jan' both have column J that has one of
three entries (ss, swc, np) on my 'reports' page i would like to see
the running total of all rows that have quot;swcquot; in column J on all pages.

since i add sheets everyday i would like to add a wildcard so that the
formula on the reports page automaticaly includes the new sheets.

*weekly totals*
i would like also to break this down by week. or perhaps it would
actually be per day. i would like to show quot;week1quot; and the next cell
show a count of rows that had quot;swcquot; for '12jan:17jan'.
if this could be self replicating that would be nice.....

any thoguhts?Here is one way. It doesn't take wildcards like you ask, you have to
maintain a list of all the worksshets in M1:Mn, so any new sheet name must
be added here

=SUMPRODUCT(COUNTIF(INDIRECT(OFFSET(M1,,,COUNTA(M1 :M1000),1)amp;quot;!J1:J1000quot;),quot;s
wcquot;))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;ninetyninequot; gt; wrote in message oups.com...
gt; i have a workbook that i add workshhets to daily, one worksheet for
gt; everyday. i would like to be able to have a reporting page that would
gt; go out to all of the pages included in the workbook and look at 1
gt; column (the same column on all pages) and come back with a count for
gt; cells that have a particular text string.
gt;
gt; *running total*
gt; i.e. page '12jan' and page '13jan' both have column J that has one of
gt; three entries (ss, swc, np) on my 'reports' page i would like to see
gt; the running total of all rows that have quot;swcquot; in column J on all pages.
gt;
gt; since i add sheets everyday i would like to add a wildcard so that the
gt; formula on the reports page automaticaly includes the new sheets.
gt;
gt; *weekly totals*
gt; i would like also to break this down by week. or perhaps it would
gt; actually be per day. i would like to show quot;week1quot; and the next cell
gt; show a count of rows that had quot;swcquot; for '12jan:17jan'.
gt; if this could be self replicating that would be nice.....
gt;
gt; any thoguhts?
gt;
For the 'weekly totals', try the following..

Let A1 contain your start date, such as 2006/1/12.

Let Column B contain the week number, such as 1 for 'Week 1', 2 for
'Week 2', and so on.

Then, enter the following formula in C1 and copy down:

=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT($A$ 1 B1*7-7amp;quot;:quot;amp;$A$1 B1*7-
7 5)),quot;ddmmmquot;)amp;quot;!J1:J1000quot;),quot;swcquot;))

Hope this helps!

In article .comgt;,
quot;ninetyninequot; gt; wrote:

gt; i have a workbook that i add workshhets to daily, one worksheet for
gt; everyday. i would like to be able to have a reporting page that would
gt; go out to all of the pages included in the workbook and look at 1
gt; column (the same column on all pages) and come back with a count for
gt; cells that have a particular text string.
gt;
gt; *running total*
gt; i.e. page '12jan' and page '13jan' both have column J that has one of
gt; three entries (ss, swc, np) on my 'reports' page i would like to see
gt; the running total of all rows that have quot;swcquot; in column J on all pages.
gt;
gt; since i add sheets everyday i would like to add a wildcard so that the
gt; formula on the reports page automaticaly includes the new sheets.
gt;
gt; *weekly totals*
gt; i would like also to break this down by week. or perhaps it would
gt; actually be per day. i would like to show quot;week1quot; and the next cell
gt; show a count of rows that had quot;swcquot; for '12jan:17jan'.
gt; if this could be self replicating that would be nice.....
gt;
gt; any thoguhts?