Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming a range | Excel Discussion (Misc queries) | |||
Range naming | Excel Discussion (Misc queries) | |||
Dynamic naming of range needed | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Naming a Range with VB | Excel Programming |