Counting a Region
 

Hi,

Is there a formula in excel, or a way to use existing formulas that can
count the number of rows and columns of region. I would like to enter this
into an Offset formula that I use in a range name. eg

Table starting at C3 and goes for x columns and y rows

RangeName = "AssetTable"

Formula is =OFFSET(C3,0,0,COUNTROWS(C3),COUNTCOLUMNS(C3))

I made up the COUNTROWS and COUNTCOLUMNS, so if I don't know how many rows
and how many columns the COUNTROWS will return the number from the cell
address C3.

I have used COUNTA many times before, however there may be other lines
adjacent to it so counting the whole row or column is not an option.

If I have to, I'll write a user defined function for it.

cheers,

Paul D Byrne.

Counting a Region
 

=OFFSET(C3,0,0,ROWS(AssetTable),COLUMNS(AssetTable ))

--
__________________________________
HTH

Bob

"Paul D Byrne." wrote in message
...
Hi,

Is there a formula in excel, or a way to use existing formulas that can
count the number of rows and columns of region. I would like to enter this
into an Offset formula that I use in a range name. eg

Table starting at C3 and goes for x columns and y rows

RangeName = "AssetTable"

Formula is =OFFSET(C3,0,0,COUNTROWS(C3),COUNTCOLUMNS(C3))

I made up the COUNTROWS and COUNTCOLUMNS, so if I don't know how many rows
and how many columns the COUNTROWS will return the number from the cell
address C3.

I have used COUNTA many times before, however there may be other lines
adjacent to it so counting the whole row or column is not an option.

If I have to, I'll write a user defined function for it.

cheers,

Paul D Byrne.

Counting a Region
 

Thanks for the reply Bob, however I get a "reference not valid" when I enter
this into the range name. Maybe I should elaborate further.

On a sheet in a new workbook I have 4 Columns, Cateogry, SubType,AssetClass
and Name Addition (there maybe more columns later) and a number of rows.

I want to create a Named Range, AssetClass that returns the reference for
the current region of that table. Usually I would add the formula below to
the range name.

=OFFSET(Sheet1!$C$3,0,0,COUNTA(Sheet1!$C:$C),COUNT A(Sheet1!$3:$3))

which would allow me to add as many rows and column as I like - as long as
there is no other data on row 3 or column c. this is the problem. Because the
range is AssetTable the command ROWS(AssetTable) is calling itself which
isn't working.


"Bob Phillips" wrote:

=OFFSET(C3,0,0,ROWS(AssetTable),COLUMNS(AssetTable ))

--
__________________________________
HTH

Bob

"Paul D Byrne." wrote in message
...
Hi,

Is there a formula in excel, or a way to use existing formulas that can
count the number of rows and columns of region. I would like to enter this
into an Offset formula that I use in a range name. eg

Table starting at C3 and goes for x columns and y rows

RangeName = "AssetTable"

Formula is =OFFSET(C3,0,0,COUNTROWS(C3),COUNTCOLUMNS(C3))

I made up the COUNTROWS and COUNTCOLUMNS, so if I don't know how many rows
and how many columns the COUNTROWS will return the number from the cell
address C3.

I have used COUNTA many times before, however there may be other lines
adjacent to it so counting the whole row or column is not an option.

If I have to, I'll write a user defined function for it.

cheers,

Paul D Byrne.