Dynamic Range Naming
 

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,

Dynamic Range Naming
 

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,

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,

Dynamic Range Naming
 

count(B:B)

will count all the filled cells in Column B. In your original example, this
will certainly give you inaccurate results.

If altering the layout as you show in your second example is an option, then
this would certainly be more amenable to using your dynamic formula (as
long as you don't put data below your data).

--
Regards,
Tom Ogilvy

"JMay" wrote in message
news:x9kBb.55088$yM6.31167@lakeread06...
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,

Dynamic Range Naming
 

Thanks Tom;
that is what I was trying to say in my original note;
appreciate your "hanging in there";
Hope you and your family have a nice holiday season,,,
JMay

"Tom Ogilvy" wrote in message
...
count(B:B)

will count all the filled cells in Column B. In your original example,
this
will certainly give you inaccurate results.

If altering the layout as you show in your second example is an option,
then
this would certainly be more amenable to using your dynamic formula (as
long as you don't put data below your data).

--
Regards,
Tom Ogilvy

"JMay" wrote in message
news:x9kBb.55088$yM6.31167@lakeread06...
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,