Range from x to first non-blank cell?
In article ,
"Bill" wrote:
Sure, I've got a spreadsheet that's got the results of a survey. On the first
page, I
cut/paste the raw survey data. On the second page of the spreadsheet, I'm
reading and
interpreting the data. Presently, every time I paste into sheet 1, I've got
to manually
increase the ranges on sheet 2 to the end of the new range. What I'd prefer,
instead, is
for sheet 2 to automatically update when the new data is inputted.
You can use a dynamic named range. So, assuming that you want to create
one for Column B on Sheet1, starting from B2...
Insert Name Define
Name: MyRange (or any other name that you want)
Refers to:
=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$65536,MATCH(9.99 999999999999E+307,Sheet
1!$B$2:$B$65536))
Click Add
*Note that if Column B contains text instead of numerical values,
change...
9.99999999999999E+307
to
REPT("z",255)
Then use MyRange in your formula. For example...
=SUM(MyRange)
This doesn't seem to work - strangely, the cell I entered it into returns the
exact text
of the formula.
The cell is probably formatted as 'Text'. Try the following...
1) Format the cell as 'General'
2) Re-enter the formula
Does this help?
|