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?

=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?

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?