Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA determine computer region | Setting up and Configuration of Excel | |||
Region | Excel Discussion (Misc queries) | |||
need to zoom in a particular region of graph | Charts and Charting in Excel | |||
Is it possible to count if within a number region? | Excel Discussion (Misc queries) | |||
how to shrink a region (because of a maverick) | Charts and Charting in Excel |