question about tricky autofill for series

[Guest]

I have a question about using autofill. I have a series of rows that contain
a final cell with a function =sum(I8*J3) and I would like to use the
autofill. however, I only want the "I" column name to change - all of the
cells should reference whatever the "I" column is but J3 should remain
constant throughout.

Whenever i try to autofill both the I and J references increase. I tried
setting the forumla for two cells and then highlighting them both to fill the
series, thinking the pattern had been established. But the J references
increased again, but only every second cell. (It showd J3 for the first two,
J5 for the next two, J7 for the next two, etc.)

Any help that anyone can offer would be greatly appreciated.

Thanks!

 

[Guest]

this is not an "autofill" problem. Autofill entails trends like 1,3,5,7,etc.
You are simply copying and pasting and that follows different rules.
=sum(I8*J3)
uses two relative references. If you copy and paste the formula to a cell
that is one row down and one colum right you will get this formula
=sum(J9*K4)
the references change according to the shift.

If you change the original formula to this
=sum($I8*$J$3)
and do the same copy (1 row down, and 1 column right), you will get this
=sum($I9*$J$3)
Only the 8 changed because that is the only part still relative. Putting
the $ in front of part of the reference causes that part to be an absolute
reference (each cell reference has two parts, column and row).

 

[Guest]

Thank you very much!

this is not an "autofill" problem. Autofill entails trends like 1,3,5,7,etc.
You are simply copying and pasting and that follows different rules.
=sum(I8*J3)
uses two relative references. If you copy and paste the formula to a cell
that is one row down and one colum right you will get this formula
=sum(J9*K4)
the references change according to the shift.

If you change the original formula to this
=sum($I8*$J$3)
and do the same copy (1 row down, and 1 column right), you will get this
=sum($I9*$J$3)
Only the 8 changed because that is the only part still relative. Putting
the $ in front of part of the reference causes that part to be an absolute
reference (each cell reference has two parts, column and row).