Forum Discussion
Dragging formulas across
Dragging SUM formula’s across… I am in ‘Sheet 2’ an trying to drag formulas across…
I want the AutoSum function to do this:
=SUM(Sheet1!B2:C2) =SUM(Sheet1!D2:E2) =SUM(Sheet1!F2:G2)
Or
=SUM(Sheet1!B2+C2) =SUM(Sheet1!D2+E2) =SUM(Sheet1!F2+G2)
But it is doing this:
=SUM(Sheet1!B2:C2) =SUM(Sheet1!C2:D2) =SUM(Sheet1!D2:E2)
Or
=SUM(Sheet1!B2+C2) =SUM(Sheet1!C2+D2) =SUM(Sheet1!D2+E2)
Please help!
TrishMcC Well, that's how dragging a formula works. Drag it one cell to the right and all column references will shift 1 column to the right as well.
You could include OFFSET in the SUM formula, like this (in Sheet2):
based on the content of Sheet1:
Now, if this it at the start of building a schedule and you don't have a huge number of columns, consider dragging as you did and then just delete every second column. Not very elegant but then you can avoid the OFFSET function.
2 Replies
TrishMcC Well, that's how dragging a formula works. Drag it one cell to the right and all column references will shift 1 column to the right as well.
You could include OFFSET in the SUM formula, like this (in Sheet2):
based on the content of Sheet1:
Now, if this it at the start of building a schedule and you don't have a huge number of columns, consider dragging as you did and then just delete every second column. Not very elegant but then you can avoid the OFFSET function.
Riny_van_Eekelen Hi Riny - thankyou so much for your feedback - the OFFSET formula is excactly what i was after for this project. I did have a little look at this function but couldn't make it work.
