Dynamic Range Naming
I rec'd data as follows:
A B
1 Mon 3
2 4
3 2
4 1
5
6 Tues 4
7 7
8 3
9 2
10 5
11 9
12 10
13
14 Wed 1
15 3
Officeworker wants to get total entries (count) per day. 4,7, & 2
Knowing the counts (rows) might change (expand downward)..
I thought I'd create a dynamic named range for Mon; one for Tues; etc
and Define Range Name sample "Mon" (in Refers to box enter)
=Offset($A$1,0,1,Counta($B:$B),1) and in cell A2 enter =count(Mon) etc
After creating "Tues" with refers to box =
Offset($A$6,0,1,Counta($B:$B),1)
and entering in cell A7 =count(Tues)
both formulas are counting non-blanks in Column B, right <<< conflict!!
This is where I decide ,, hummmmm to get around this I should
Lay this data out as follows:
A B C D E F
1 Mon 3 Tues 4 Wed 1
2 4 7 3
3 2 3
4 1 2
etc
Now having a formula in C2 =count(Tues) where
Tues refers to = Offset($C$1,0,1,Counta($D:$D),1)
More Confused yet?
If True then "Sorry"
Else "hummmm"
End If
Do you now see/comprehend my mental twistedness?
Tks,
"Tom Ogilvy" wrote in message
...
Your description is very confusing.
your named range will refer to column B (columns are vertical).
Not sure how this makes you layout horizontal or what your separate
formulas
are.
--
Regards,
Tom Ogilvy
"JMay" wrote in message
news:E_iBb.54786$yM6.11248@lakeread06...
have spent some time this AM on Subject using sample
=OFFSET(Sheet1!$A$1,0,1,COUNTA(Sheet1!$B:$B),1)
The one restriction I'm presently wrestling with is the fact
that structurally I must arrange my layout horizontally versus
Vertically
because of the use of the Counta(B:B) thing. If I go Vertically my
separate formulas would each refer to B:B and this would create
a problem, right? -- So going Horizontally, eliminates the problem,
right?
a bit confused, as if you couldn't already tell.. LOL
Can someone unconfuse me on this subject?
TIA,
|