Copy paste formula problem

[Gazzal]

I have a years results table pulling data from another weekly sheet with this formula

=SUM('2012'!B21,'2012'!C21),"")

I would like to copy/paste this to each cell in row but offset the columns by one

eg next cell;

=SUM('2012'!D21,'2012'!E21),"")

but what I get is;

=SUM('2012'!C21,'2012'!D21),"")

Is there a way to do this?

 

[SirJB7]

Hi, Gazzal!


Not directly, but has a workaround.


As you have relative cell references in your formula (B21 & C21) when you copy to next cell in row Excel will keep fixed the row (21) and shift one column (to C & D) because you're pasting in a "one column to the right" cell. To obtain what you want you should copy formulas to a cell two columns to the right. And then you can hide the intermediate unwanted column.


Regards!

 

[Gazzal]

Thanks SirJB7

Would be just as much work as Copy/paste formula to many cells on outside row and cut/paste every second one back, but that's labor intensive, I thought there might be a short cut the experts know of??

 

[Hui]

Gazzal


A couple of things


Your formula

=SUM('2012'!B21,'2012'!C21),"")


Can be simplified to

=SUM('2012'!B21:C21)


Assuming you are starting in Row 2

=SUM(OFFSET('2012'!$B$21:$C$21,,2*(ROW()-ROW($A$2))))

should do the trick

As you copy it down it will increment the offset 2 rows to the right for each row you copy it down

 

[SirJB7]

@Gazzal

Hi!

Never dare to not read Hui...

Regards!