Prevent row increase in formula
Posted by EXCEL NEWBIE on February 12, 2002 12:11 PM
I want to paste a formula (=+C1) into column A (every 10th row, but I only want the formula to increase 1 increment. In other words, starting with A3, I want it to copy the contents of C, then in A13, I want to copy the contents of C2, not C13 which is what happens when I paste in the formula. Any help is appreciated.
Posted by IML on February 12, 2002 12:26 PM
How about
=OFFSET($C$1,(ROW()-3)/10,0)
good luck
Posted by Newbie on February 12, 2002 12:30 PM
THANK YOU IML, as always, your true genius saves the day!!!
Posted by Mark W. on February 12, 2002 12:36 PM
Assuming that your column C values begin in cell C1.
Enter the formula, =INDIRECT("C"&COUNT($A$1:A2)+1),
into cell A3. Copy cells A3:A12, select cell A13,
Shift+Click to extend the selection to a number
of cells evenly divisible by 10, and Paste.
Posted by Mark W. on February 12, 2002 12:38 PM
Very nice!!! Obviously, I was solving a different problem! : ) [nt]
