SOLVED

Dragging formulas across

Copper Contributor

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!

2 Replies
best response confirmed by allyreckerman (Microsoft)
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):

Screenshot 2021-07-25 at 08.55.49.png

based on the content of Sheet1:

Screenshot 2021-07-25 at 08.59.11.png

 

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.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
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):

Screenshot 2021-07-25 at 08.55.49.png

based on the content of Sheet1:

Screenshot 2021-07-25 at 08.59.11.png

 

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.

View solution in original post