Copy drag formula down cells
 

I have a formula in column D. It references cells from another sheet (Sales)
in the same workbook that are in column C.
I want to drag the formula down column D and have it reference cells moving
across the columns, referencing the same rows from the other sheet.
EG: SUM(Sales!C63:C64)/Sales!C60 becomes SUM(Sales!D63:D64)/Sales!D61.

At the moment, when I drag this formula down the column the row data changes
but the column data remains the same. The opposite to what I want to happen.
--
Thank you,

Colin.

Copy drag formula down cells
 

EG: SUM(Sales!C63:C64)/Sales!C60 becomes SUM(Sales!D63:D64)/Sales!D61.

Think you meant SUM(Sales!D63:D64)/Sales!D60 ..

If so, try this in the startcell in col D:
=SUM(OFFSET(Sales!C$64,,ROWS($1:1)-1,-2))/OFFSET(Sales!C$60,,ROWS($1:1)-1,)

Above returns:
=SUM(Sales!C63:C64)/Sales!C60

When you copy it down, it'll return:
=SUM(Sales!D63:D64)/Sales!D60

and so on, as desired.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Colin" wrote:
I have a formula in column D. It references cells from another sheet (Sales)
in the same workbook that are in column C.
I want to drag the formula down column D and have it reference cells moving
across the columns, referencing the same rows from the other sheet.
EG: SUM(Sales!C63:C64)/Sales!C60 becomes SUM(Sales!D63:D64)/Sales!D61.

At the moment, when I drag this formula down the column the row data changes
but the column data remains the same. The opposite to what I want to happen.
--
Thank you,

Colin.

Copy drag formula down cells
 

I'm learning something new every time I ask a question, many thanks for this.
I'm glad I asked 'cause I'd never have worked that one out on my own!
--
Thank you,

Colin.


"Max" wrote:

EG: SUM(Sales!C63:C64)/Sales!C60 becomes SUM(Sales!D63:D64)/Sales!D61.

Think you meant SUM(Sales!D63:D64)/Sales!D60 ..

If so, try this in the startcell in col D:
=SUM(OFFSET(Sales!C$64,,ROWS($1:1)-1,-2))/OFFSET(Sales!C$60,,ROWS($1:1)-1,)

Above returns:
=SUM(Sales!C63:C64)/Sales!C60

When you copy it down, it'll return:
=SUM(Sales!D63:D64)/Sales!D60

and so on, as desired.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Colin" wrote:
I have a formula in column D. It references cells from another sheet (Sales)
in the same workbook that are in column C.
I want to drag the formula down column D and have it reference cells moving
across the columns, referencing the same rows from the other sheet.
EG: SUM(Sales!C63:C64)/Sales!C60 becomes SUM(Sales!D63:D64)/Sales!D61.

At the moment, when I drag this formula down the column the row data changes
but the column data remains the same. The opposite to what I want to happen.
--
Thank you,

Colin.

Copy drag formula down cells
 

glad it helped, Colin.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Colin" wrote in message
...
I'm learning something new every time I ask a question, many thanks for
this.
I'm glad I asked 'cause I'd never have worked that one out on my own!
--
Thank you,

Colin.