SOLVED

Dragging formulas across

%3CLINGO-SUB%20id%3D%22lingo-sub-2583837%22%20slang%3D%22en-US%22%3EDragging%20formulas%20across%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2583837%22%20slang%3D%22en-US%22%3E%3CP%3EDragging%20SUM%20formula%E2%80%99s%20across%E2%80%A6%20I%20am%20in%20%E2%80%98Sheet%202%E2%80%99%20an%20trying%20to%20drag%20formulas%20across%E2%80%A6%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20the%20AutoSum%20function%20to%20do%20this%3A%3C%2FP%3E%3CP%3E%3DSUM(Sheet1!B2%3AC2)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DSUM(Sheet1!D2%3AE2)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DSUM(Sheet1!F2%3AG2)%3C%2FP%3E%3CP%3EOr%3C%2FP%3E%3CP%3E%3DSUM(Sheet1!B2%2BC2)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DSUM(Sheet1!D2%2BE2)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DSUM(Sheet1!F2%2BG2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20is%20doing%20this%3A%3C%2FP%3E%3CP%3E%3DSUM(Sheet1!B2%3AC2)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DSUM(Sheet1!C2%3AD2)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DSUM(Sheet1!D2%3AE2)%3C%2FP%3E%3CP%3EOr%3C%2FP%3E%3CP%3E%3DSUM(Sheet1!B2%2BC2)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DSUM(Sheet1!C2%2BD2)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3DSUM(Sheet1!D2%2BE2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2583837%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2584050%22%20slang%3D%22en-US%22%3ERe%3A%20Dragging%20formulas%20across%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2584050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1111880%22%20target%3D%22_blank%22%3E%40TrishMcC%3C%2FA%3E%26nbsp%3BWell%2C%20that's%20how%20dragging%20a%20formula%20works.%20Drag%20it%20one%20cell%20to%20the%20right%20and%20all%20column%20references%20will%20shift%201%20column%20to%20the%20right%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20include%20OFFSET%20in%20the%20SUM%20formula%2C%20like%20this%20(in%20Sheet2)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-07-25%20at%2008.55.49.png%22%20style%3D%22width%3A%20542px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F298212iD721E6F36C58A03F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-07-25%20at%2008.55.49.png%22%20alt%3D%22Screenshot%202021-07-25%20at%2008.55.49.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ebased%20on%20the%20content%20of%20Sheet1%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-07-25%20at%2008.59.11.png%22%20style%3D%22width%3A%20726px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F298214iD95DB5373B95A301%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-07-25%20at%2008.59.11.png%22%20alt%3D%22Screenshot%202021-07-25%20at%2008.59.11.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.