Offset function and Dynamic Ranges
 

I just saw an example of this formula in an online newsletter:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A$1:$A$19),COUN TA(Data!$1:$1))
I was trying it out and noticed that you have to place it with the upper
left cell in cell A1 of a worksheet for it to reproduce the data that it
refers to. Otherwise the #VALUE error appears. This is not how the OFFSET
function usually works for me and it seems to have something to do with the
COUNTA function appearing in the height and width arguments. I usually don't
use the height and width arguments at all. Could someone explain why this is
so and if there is a way to overcome this restriction?
Thanks for your help,
Sandy

Offset function and Dynamic Ranges
 

It is not just A1, but any cell that maps onto that dynamic range, as it
gets the value relative to the cell the formula is in.

For example, if the data on the Data worksheet has 10 rows of data in column
A, and 5 columns in row 1, this formula will work in any cell in the range
A1:E10.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"SandyLACA" wrote in message
...
I just saw an example of this formula in an online newsletter:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A$1:$A$19),COUN TA(Data!$1:$1))
I was trying it out and noticed that you have to place it with the upper
left cell in cell A1 of a worksheet for it to reproduce the data that it
refers to. Otherwise the #VALUE error appears. This is not how the OFFSET
function usually works for me and it seems to have something to do with
the
COUNTA function appearing in the height and width arguments. I usually
don't
use the height and width arguments at all. Could someone explain why this
is
so and if there is a way to overcome this restriction?
Thanks for your help,
Sandy

Offset function and Dynamic Ranges
 

This formula is commonly used to dynamically name ranges of data that
expand and contract (Mostly expand) frequently. This way you can
always reference the data via the range name in formulas and as data
sources for pivot tables etc.


SandyLACA Wrote:
I just saw an example of this formula in an online newsletter:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A$1:$A$19),COUN TA(Data!$1:$1))
I was trying it out and noticed that you have to place it with the
upper
left cell in cell A1 of a worksheet for it to reproduce the data that
it
refers to. Otherwise the #VALUE error appears. This is not how the
OFFSET
function usually works for me and it seems to have something to do with
the
COUNTA function appearing in the height and width arguments. I usually
don't
use the height and width arguments at all. Could someone explain why
this is
so and if there is a way to overcome this restriction?
Thanks for your help,
Sandy


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=567656