| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range / offset
I am trying to create a dynamic range.
so far i have had luck with the suggestion form another user to use: =OFFSET($C$8,0,0,COUNTA($C:$C),1) that works when the range is in one column. what do i change if i want to expand the range to say, AF? I have tried =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1) and =OFFSET($C$8,0,0,COUNTA($C:$AF),1) but neither seemed to work. what am i missing? thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
named range / offset
and as usual as soon as i post i look at the help for Offset.....
=OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($C:$AF)) gives you both height and width "Gixxer_J_97" wrote: I am trying to create a dynamic range. so far i have had luck with the suggestion form another user to use: =OFFSET($C$8,0,0,COUNTA($C:$C),1) that works when the range is in one column. what do i change if i want to expand the range to say, AF? I have tried =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1) and =OFFSET($C$8,0,0,COUNTA($C:$AF),1) but neither seemed to work. what am i missing? thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
named range / offset
always a good idea to check help. (first? :) however you've got it almost right.. the width will get TOO big and the formula will be too slow. you'll need as wide as the number of headers, correct? assuming those are in row 8.. =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($8:$8)) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Gixxer_J_97 wrote : and as usual as soon as i post i look at the help for Offset..... =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($C:$AF)) gives you both height and width "Gixxer_J_97" wrote: I am trying to create a dynamic range. so far i have had luck with the suggestion form another user to use: =OFFSET($C$8,0,0,COUNTA($C:$C),1) that works when the range is in one column. what do i change if i want to expand the range to say, AF? I have tried =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1) and =OFFSET($C$8,0,0,COUNTA($C:$AF),1) but neither seemed to work. what am i missing? thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
named range / offset
hmmmm - i see what you mean, but i don't quite understand why the width would
get too big. i do see that it is much easier to just count the headers - but i was thinking that it would be the same as counting C:AF my headers are in row 7, the data i'll actually be using starts in row 8 from C:AF i'll try your version and go from there thanks for the help! J "keepITcool" wrote: always a good idea to check help. (first? :) however you've got it almost right.. the width will get TOO big and the formula will be too slow. you'll need as wide as the number of headers, correct? assuming those are in row 8.. =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($8:$8)) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Gixxer_J_97 wrote : and as usual as soon as i post i look at the help for Offset..... =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($C:$AF)) gives you both height and width "Gixxer_J_97" wrote: I am trying to create a dynamic range. so far i have had luck with the suggestion form another user to use: =OFFSET($C$8,0,0,COUNTA($C:$C),1) that works when the range is in one column. what do i change if i want to expand the range to say, AF? I have tried =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1) and =OFFSET($C$8,0,0,COUNTA($C:$AF),1) but neither seemed to work. what am i missing? thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
named range / offset
counta(c:af) counts all filled cells in all rows in the range.
so the width might be a bit bigger than you bargained for. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Gixxer_J_97 wrote : hmmmm - i see what you mean, but i don't quite understand why the width would get too big. i do see that it is much easier to just count the headers - but i was thinking that it would be the same as counting C:AF my headers are in row 7, the data i'll actually be using starts in row 8 from C:AF i'll try your version and go from there thanks for the help! J "keepITcool" wrote: always a good idea to check help. (first? :) however you've got it almost right.. the width will get TOO big and the formula will be too slow. you'll need as wide as the number of headers, correct? assuming those are in row 8.. =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($8:$8)) -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Gixxer_J_97 wrote : and as usual as soon as i post i look at the help for Offset..... =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($C:$AF)) gives you both height and width "Gixxer_J_97" wrote: I am trying to create a dynamic range. so far i have had luck with the suggestion form another user to use: =OFFSET($C$8,0,0,COUNTA($C:$C),1) that works when the range is in one column. what do i change if i want to expand the range to say, AF? I have tried =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1) and =OFFSET($C$8,0,0,COUNTA($C:$AF),1) but neither seemed to work. what am i missing? thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
named range / offset
now that makes perfect sense!
thanks! J "keepITcool" wrote: counta(c:af) counts all filled cells in all rows in the range. so the width might be a bit bigger than you bargained for. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Gixxer_J_97 wrote : hmmmm - i see what you mean, but i don't quite understand why the width would get too big. i do see that it is much easier to just count the headers - but i was thinking that it would be the same as counting C:AF my headers are in row 7, the data i'll actually be using starts in row 8 from C:AF i'll try your version and go from there thanks for the help! J "keepITcool" wrote: always a good idea to check help. (first? :) however you've got it almost right.. the width will get TOO big and the formula will be too slow. you'll need as wide as the number of headers, correct? assuming those are in row 8.. =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($8:$8)) -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Gixxer_J_97 wrote : and as usual as soon as i post i look at the help for Offset..... =OFFSET($C$8,0,0,COUNTA($C:$C),COUNTA($C:$AF)) gives you both height and width "Gixxer_J_97" wrote: I am trying to create a dynamic range. so far i have had luck with the suggestion form another user to use: =OFFSET($C$8,0,0,COUNTA($C:$C),1) that works when the range is in one column. what do i change if i want to expand the range to say, AF? I have tried =OFFSET($C$8:$AF$8,0,0,COUNTA($C:$C),1) and =OFFSET($C$8,0,0,COUNTA($C:$AF),1) but neither seemed to work. what am i missing? thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Named range is hidden when using OFFSET() | Excel Discussion (Misc queries) | |||
| named range row offset | Excel Worksheet Functions | |||
| Dynamic named range & Offset | Excel Discussion (Misc queries) | |||
| Offset delivers value error with Named range | Excel Worksheet Functions | |||
| named range, offset self-reference | Excel Discussion (Misc queries) | |||
![Gixxer_J_97[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
