Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
question about tricky autofill for series
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
question about tricky autofill for series
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). "Daesthai" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
question about tricky autofill for series
Thank you very much!
"Sloth" wrote: 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). "Daesthai" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tricky data entry question | Excel Discussion (Misc queries) | |||
autofill question | Excel Discussion (Misc queries) | |||
Autofill question | Excel Worksheet Functions | |||
Tricky Question | Excel Worksheet Functions | |||
rota question - very tricky... | Excel Discussion (Misc queries) |