dynamic range

[R.VENKATARAMAN]

there is an excellent formula (my notes say by Dalgleish) for a dynamic data
source for pivot tables etc.

you name the range by defining a name (insert,name,define) and writing in
the REFER TO box the following formula

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7)
7 is the no. of columns. if you change the no. of columns you have to change
7 to the new number

assuming the range starts in B2
can we write
=offset(data!$B$2,0,0,countA(data!$B:$B),countA(data!$2:$2))

you need not even write the whole formula wherein some typing error may
occur

then you type
=OFFSET(<click the first cell of the range>,0,0,COUNTA(<click the first
column of the range>),COUNTA(<click the first row of the range>))

the symbols < and > are used only to separate the instructions .
In the above example it will be
=OFFSET(<click B2>,0,0,COUNTA(<click column B>),COUNTA(<click row 2>))

perhaps I am reinventing the wheel and some experts have already given this
solution.
Besides as I am not an expert I tried to find out how to write the formula
without making typing errors

however I shall be extremely thankful for the experts' comments (even
adverse) or any error in the logic
It will clear my ideas.
thanks.

 

[Don Guillett]

assuming the range starts in B2

can we write
=offset(data!$B$2,0,0,countA(data!$B:$B),countA(data!$2:$2))

should be OK