Home 
Search 
Today's Posts 
#1




how do I make a formula NOT change when the data range is moved?
In Excel 2002, if you create a formula that uses a range of data, i.e.,
sum(a1:e1), when you move the data in that range to another location, the formula changes to the new location of the data range. In other words, if cell f1 contains the formula sum(a1:e1), and I move the contents of a1:e1 to a4:e4, the formula in cell f1 changes to sum(a4:e4). Is there some way to make the formula remain sum(a1:e1), even when the data is moved? 
#2




=SUM(INDIRECT("A1:E1"))
 HTH RP (remove nothere from the email address if mailing direct) "Alida Andrews" <Alida wrote in message ... In Excel 2002, if you create a formula that uses a range of data, i.e., sum(a1:e1), when you move the data in that range to another location, the formula changes to the new location of the data range. In other words, if cell f1 contains the formula sum(a1:e1), and I move the contents of a1:e1 to a4:e4, the formula in cell f1 changes to sum(a4:e4). Is there some way to make the formula remain sum(a1:e1), even when the data is moved? 
#3




Use absolute references
sum($a$1:$e$1) or move it with the mouse (select f1, move the cursor to any cell border and when it changes to a 4 arrow left click and drag)  Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Alida Andrews" <Alida wrote in message ... In Excel 2002, if you create a formula that uses a range of data, i.e., sum(a1:e1), when you move the data in that range to another location, the formula changes to the new location of the data range. In other words, if cell f1 contains the formula sum(a1:e1), and I move the contents of a1:e1 to a4:e4, the formula in cell f1 changes to sum(a4:e4). Is there some way to make the formula remain sum(a1:e1), even when the data is moved? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Help with data not getting plotted  Charts and Charting in Excel  
How to change cell data from all upper to title case  Excel Discussion (Misc queries)  
Vlookup of parts of a word in a range of Data  Excel Discussion (Misc queries)  
Charting data ranges that change  Charts and Charting in Excel  
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula?  Excel Discussion (Misc queries) 