how to change range of cells

[0406excel]

I am a beginning Excel user.

Is there anyway to change the range of cells in a Sum formula by using the value from another cell?

For example, I want to change sum(a9..a4000) to sum(a9..a1000). Right now I have to manually enter and change 4000 to 1000. I would like to enter in cell a1 the number 1000 and have the sum range drop to 1000.

However, I get error messages like #NAME? if I try to amend sum(a9..a4000) to sum((a9..a(a1)) or sum(a9..a+a1)

[JeanRage]

Hi,

You can use the keys : Control H
and replace all instances of 4000 by 1000 ...

HTH

[phil_b]

try this:

=SUM(INDIRECT("A1:A" &D3))

Replace values with your range and D3 with the value you want the range extended to.

[0406excel]

It works! Thank you so much.