Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel insits that I use absolute cell references | Excel Discussion (Misc queries) | |||
Automatically Changing Cell Reference's when Pasting in Excel | Excel Discussion (Misc queries) | |||
how do i copy a formula when cell references r not together | New Users to Excel | |||
Cell references change when entering new data | New Users to Excel | |||
Absolute cell references and subsequent problems. | Excel Discussion (Misc queries) |