Extending a Named Range when Adding Data
Posted by Caleb N. Diffell on November 01, 2001 7:39 AM
I have a range that extends over several columns and several hundred rows. I have the range named so I can use the name ("data", in this case) in a few DSUM formulas. But sometimes I need to add rows to the bottom of the data. Unfortunately, my named range does not automatically extend itself to include the added row(s). Is there a way for it to do this automatically?
Thanks in advance for any advice!
Posted by lenze on November 01, 2001 8:06 AM
Two options.
Use the Data form option to enter new recoeds. This should extend the range automatically depending on the Ecel version (You may have to rename your range "database".
Best: Use a dynamic range name. See formula below
Dynamic Range Formula
=Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A$1:$A$65536)-1,COUNTA(Sheet1!$1:$1)-1)
Note: $A$1= Anchor Cell Address (Change as needed)
Sheet1!$1:$1 Anchor Row Number (Change as needed)
The above formula gives a dynamic range that expands both by rows and columns, using the entire column or row. To limit the number of rows, change 65536 to a lower number (ex. 1000 will limit the range to 999 rows) To limit the number of columns, change the Sheet1!$1:$1 to Sheet1!$A$1:$Col :$1, where Col is the column letter of the last column you want in the range. To use, Choose Insert<Name<Define. Type a name for your range in the name box and then enter the above formula (with any adjustments) in the refers to field.
