How to modify fill handle properties?

[sunny]

Hi,

I am facing a problem using the fill handle. Theproblem is explained
below.

I have two worksheets -sheet1 and sheet2. I sheet-1 , i have the
following data (in rows R1, R2, R3 and columns C1, C2,C3 AND C4 .

C1 C2 C3 C4
R1 1/16/2006 10:30 375.2 372.05 374.05
R2 1/16/2006 11:00 374.4 370.1 370.1
R3 1/16/2006 11:30 370.2 365.8 366
R4 1/16/2006 12:00 371.2 360.8 365

In sheet-2 , i am trying to fill a column col2 with the following
formulas:
Col2
Row1: (R1+R2)-C2-sheet1
Row2: (R3+R4)-C2-sheet1

I want each subsequent row to have two consecutive rows from the
previous cell. For example, if Row1 cell contains R1+R2 , then i want
the next cell to have R3+R4 in Row2 cell of sheet2.
But what excel does when i drag the formula down in the column is given
below:
Col2
Row1: (R1+R2)-C2-sheet1
Row2: (R2+R3)-C2-sheet1

I want [(R3+R4)-C2-SHEET1] instead of [(R2+R3)-C2-sheet1]. is there a
way to solve this problem?

 

[Ardus Petus]

Try using the function ROW()

HTH

 

[vezerid]

I am not sure I fully understand what you are subtracting. But I know
for sure how to get the expression R1+R2, then R3+R4 etc, thus you can
include the following expression in your formula

OFFSET('Sheet1'!$A$1,2*(ROW()-1),0)+OFFSET('Sheet1'!$A$1,2*(ROW()-1)+1,0)

and then subtract as necessary.

HTH
Kostis Vezerides

 

[ashish]

I am not sure I fully understand what you are subtracting. But I know
for sure how to get the expression R1+R2, then R3+R4 etc, thus you can
include the following expression in your formula

OFFSET('Sheet1'!$A$1,2*(ROW()-1),0)+OFFSET('Sheet1'!$A$1,2*(ROW()-1)+1,0)

and then subtract as necessary.

HTH
Kostis Vezerides

Hi,

I am not subtracting anything. Here is the problem again-

this is sheet 1
C1
R1 10
R2 20
R3 30
R4 40
R5 50
R6 60
R7 70
R8 80

In sheet 2, I want cell Row1col1 to represent addition of cell R1C1 and
cell R2C1 of sheet 1
cell Row2col1 to represent addition of cell R3C1 and cell R4C1 of sheet
1
cell Row3col1 to represent addition of cell R5C1 and cell R6C1 of sheet
1
and so on...........

What's happening in sheet2 is after i put the formula in cell row1col1
(adding cell R1C1 and cell R2C1 of sheet1) and dragging it down,
cell Row2col1 does addition of cell R2C1 and cell R3C1
cell Row3col1 does addition of cell R3C1 and cell R4C1
and so on..........

Is there any way out to do such kind of addition.

Thanks

ashish