Dynamic Range Naming

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

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,
 
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.
 
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,
 
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).
 
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
 
Back
Top