• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy paste formula problem

G

Gazzal

New Member
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?
 
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!
 
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??
 
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
 
You must log in or register to reply here.
Back
Top