Redefining Last Cell
Posted by Vicky Gillen on June 19, 2001 2:50 AM
When my data range changes on a worksheet, how can I get Excel to realise that the Last Cell is no longer, for instance, Row 100 Column 100, but Row 50 Column 50?
Posted by Aladin Akyurek on June 19, 2001 3:21 AM
Vicky
You can define a named dynamic range.
Lets say that, on some sheet, the column A contains data from A1 on whose last cell having a value regularly changes by additions and/or deletions.
Activate that cell, activate the option Insert|Name|Define. Enter a name, e.g., NAMES, DATES, etc, as the value of "Names in Workbook" and enter the following formula as the value of "Refers to":
=OFFSET(x!$A$1,0,0,COUNTA($A:$A),1)
where x is the name of the sheet whose column contains the data of interest.
For other examples, see
http://www.cpearson.com/excel/
Aladin
Posted by Mark W. on June 19, 2001 8:02 AM
ASAP Utilities has a "Reset Excel's last cell"
(Control+Shift+R) command in its Sheets menu.
This is a free utility that can be obtained at
www.asap-utilities.com
