I have a formula that I want to spread down 1000 rows.

=Change!$D$2

However, when I try to drag this formula into the other rows, the formula
stays the same when I really want it to be:

=Change!$D$3
=Change!$D$4
=Change!$D$5
=Change!$D$6 and so on.

I also tried to manually enter the formula for the first 5 and load it that
way, but it doesn't work. How can I fix this? I can not change this formula
as it is linked to another worksheet the keeps getting re arranged.

Thanks.

To drag and fill the formula the $ sign before the row number needs to be
removed. Using =Change!$D2 in the first cell and then dragging down rows will
work.

The $ sign fixes the cell reference, so if the formula aboce is dragged
across columns the column reference is fixed, but if =Change!D2 (without any
$ signs) is used the formula when dragged across columns or rows will now
change column reference or row reference.

This is the difference between an absolute reference and a relative reference.

--
Kind regards

Rik


"Smirlface" wrote:

I have a formula that I want to spread down 1000 rows.

=Change!$D$2

However, when I try to drag this formula into the other rows, the formula
stays the same when I really want it to be:

=Change!$D$3
=Change!$D$4
=Change!$D$5
=Change!$D$6 and so on.

I also tried to manually enter the formula for the first 5 and load it that
way, but it doesn't work. How can I fix this? I can not change this formula
as it is linked to another worksheet the keeps getting re arranged.

Thanks.

=Change!$D2

Column will remain fixed, row will increment as you drag/copy down.

See help on relative and absolute references.


Gord Dibben MS Excel MVP


On Tue, 15 Dec 2009 15:28:01 -0800, Smirlface
wrote:

I have a formula that I want to spread down 1000 rows.

=Change!$D$2

However, when I try to drag this formula into the other rows, the formula
stays the same when I really want it to be:

=Change!$D$3
=Change!$D$4
=Change!$D$5
=Change!$D$6 and so on.

I also tried to manually enter the formula for the first 5 and load it that
way, but it doesn't work. How can I fix this? I can not change this formula
as it is linked to another worksheet the keeps getting re arranged.

Thanks.

Thank you! that works :)

"Rik_UK" wrote:

To drag and fill the formula the $ sign before the row number needs to be
removed. Using =Change!$D2 in the first cell and then dragging down rows will
work.

The $ sign fixes the cell reference, so if the formula aboce is dragged
across columns the column reference is fixed, but if =Change!D2 (without any
$ signs) is used the formula when dragged across columns or rows will now
change column reference or row reference.

This is the difference between an absolute reference and a relative reference.

--
Kind regards

Rik


"Smirlface" wrote:

I have a formula that I want to spread down 1000 rows.

=Change!$D$2

However, when I try to drag this formula into the other rows, the formula
stays the same when I really want it to be:

=Change!$D$3
=Change!$D$4
=Change!$D$5
=Change!$D$6 and so on.

I also tried to manually enter the formula for the first 5 and load it that
way, but it doesn't work. How can I fix this? I can not change this formula
as it is linked to another worksheet the keeps getting re arranged.

Thanks.