cell references
 

Hello,
I would like to find out how I could easily change a cell reference say in a
sum formula, without having to go into the formula and changing it.
As a table grows longer I want the sum formula to read in the new rows
without having to go into each sum formula.
Say the formula is =sum(a10:a15) the next day it grow by 2 more rows and it
should be =sum(a10:a17).
To change all the various types of formulas I am using, is taking a long time.
Is there another way of formula inserts perhaps, that I could use that I
have the number of rows in a cell z1 and I could change all the other
formulas by changing the number in z1 once and it references to all the other
formulas (These are all at the top of each table).
You help will be greatly appreciated.

Thank you
Maxwell

cell references
 

Hi

This should automatically be taken care of if you INSERT rows. When
inserting rows and you used to sum to 15 you can insert two rows at 15
(but unfortunately your number in 15 will shift down).

The alternative is to make your sum until 16 even though your last
number is in 15. Then for future updates, you can insert two rows in 16
and the last number will stay in 15 but your formula would include two
more rows for your extra data.

cell references
 

the easiest way is to just define a name for the range.

editnamedefinename itin the refers to box
=offset($a$10,0,0,counta($a:$a)-9,1)
now
=sum(mynamedrange)
--
Don Guillett
SalesAid Software

"Max" wrote in message
...
Hello,
I would like to find out how I could easily change a cell reference say in
a
sum formula, without having to go into the formula and changing it.
As a table grows longer I want the sum formula to read in the new rows
without having to go into each sum formula.
Say the formula is =sum(a10:a15) the next day it grow by 2 more rows and
it
should be =sum(a10:a17).
To change all the various types of formulas I am using, is taking a long
time.
Is there another way of formula inserts perhaps, that I could use that I
have the number of rows in a cell z1 and I could change all the other
formulas by changing the number in z1 once and it references to all the
other
formulas (These are all at the top of each table).
You help will be greatly appreciated.

Thank you
Maxwell

cell references
 

In case you are on Excel 2003, convert the area of interest into a List
by means of Data|List|Create List. The formulas referiing to the list or
parts thereof will adjust automatically to changes.

Max wrote:
Hello,
I would like to find out how I could easily change a cell reference say in a
sum formula, without having to go into the formula and changing it.
As a table grows longer I want the sum formula to read in the new rows
without having to go into each sum formula.
Say the formula is =sum(a10:a15) the next day it grow by 2 more rows and it
should be =sum(a10:a17).
To change all the various types of formulas I am using, is taking a long time.
Is there another way of formula inserts perhaps, that I could use that I
have the number of rows in a cell z1 and I could change all the other
formulas by changing the number in z1 once and it references to all the other
formulas (These are all at the top of each table).
You help will be greatly appreciated.

Thank you
Maxwell

cell references
 

Another way is to highlight the cells with formulae in and do CTRL-H
(Find & Replace):

Find What?: a15
Replace With: a17

then click Replace All. You can repeat this for other formulae which
might have different cell references.

Hope this helps.

Pete