#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pete_UK
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel insits that I use absolute cell references Espen Excel Discussion (Misc queries) 6 January 5th 17 06:47 AM
Automatically Changing Cell Reference's when Pasting in Excel No I'm Spartacus Excel Discussion (Misc queries) 0 February 13th 06 02:01 PM
how do i copy a formula when cell references r not together jon104 New Users to Excel 1 January 9th 06 04:25 PM
Cell references change when entering new data [email protected] New Users to Excel 2 May 6th 05 07:48 PM
Absolute cell references and subsequent problems. Pank Mehta Excel Discussion (Misc queries) 2 April 19th 05 11:38 AM


All times are GMT +1. The time now is 09:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"