Jul 24 2021 08:14 PM
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!
Jul 25 2021 12:00 AM - edited Jul 25 2021 12:02 AM
Solution@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.
Jul 25 2021 06:29 PM
@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.
Jul 25 2021 12:00 AM - edited Jul 25 2021 12:02 AM
Solution@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.