Copy drag formula down cells

C

Colin

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.
 
M

Max

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.
 
C

Colin

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top