Counting populated cells in another worksheet
 

I have 3 worksheets, 1 containing data for 2006, one containing data for
2007, and the third is suppose to sum year-to date data from each sheet.
Column A of both data worksheets is populated with Week 1 through Week 52.
Column B of the 2006 worksheet is populated for all the weeks, but for the
2007 worksheet, is only populated for the first 2 weeks. In cell B18 of my
Totals worksheet I have the date of my current week, in this case 1/13/07. I
wanted these formulas to be dynamic so that when the date changes to 2008,
the formulas would reference sheets 2007, and 2008. I nearly had this
figured out, but I cant seem to get around referencing the 2007 worksheet
when I do a COUNTA to determine the number of populated weeks

2006 2007
Columns: A B A
B
Row 1 Direct
Direct
Row 2 New
New
Row 3 Week 1 2 Week 1
5
Row 4 Week 2 4 Week 2
5
Row 5 Week 3 7 Week 3
(Blank)
. . .
. . .

Totals Worksheet
Columns: F G
Row 5 Current Year to Date Previous Year to Date
Row 6 10 6

Here are the formulas I have so far:
Current Year to Date (Cell F6):
=SUM(INDIRECT(ADDRESS(3,ROW(6:6)-4,4,,YEAR($B$18))):INDIRECT(ADDRESS(COUNTA
('2007'!$B:$B),ROW(6:6)-4,4,,YEAR($B$18))))

Previous Year to Date (Cell G6):
=SUM(INDIRECT(ADDRESS(3,ROW(6:6)-4,4,,YEAR($B$18)-1)):INDIRECT(ADDRESS
(COUNTA('2007'!$B:$B),ROW(6:6)-4,4,,YEAR($B$18)-1)))

There is also additional data in column C through E that I wanted to be able
to reference as well by copying the formula down, which is why a reference
the column number as €œROW(6:6)-4€�.

Any ideas on how to get around this or am I doing this all wrong to begin
with?

Thanks.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200705/1