# Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

1. ## Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Hi,

I need some help with copying a formula which adds columns from another Sheet.

I have 2x B2B and 1x B2C.

I would like to add B2B sales togetherr in first column and then =sales 3 on second. I want to repeat this formula by dragging it to the right and not type formula in each cell by myself.

#Sheet 1
A B C D E F G
1 Period 1 2
2 Sales B2B B2B B2C B2B B2B B2C.. n1+
3 Values 1 2 3 4 5 6

#Sheet2 - Dasboard
A B C D E F G
1 Period 1 2
2 B2B =Sheet1!B3+Sheet1!C3 (WHEN I TRY TO COPY THIS FURTHER IT JUST ADDS 1, but what I had preferred is that it took next B2B sales from Period 2 etc: =Sheet1!E3+Sheet1!F3)
3 B2C =Sheet1!D3

I tried to explain it as easy as possible.

Thanks in advance for usefull tips!

- Hens

2. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

I have now enclosed an example, with comment.

- Hens

4. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

in b4 =SUM(OFFSET('Inndata-Sheet1'!\$B5,0,(COLUMN(A1)-1)*1.5,1,2))
in c4 ==SUM(OFFSET('Inndata-Sheet1'!\$D5,0,(COLUMN(A1)-1)*1.5,))

5. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Thanks for fast respond.

Enclosed is a new version of the dashboard - which may be of better use.
Do you have similar formulas for that view? Same INNDATA follows. Same principle of just copying formulas to next periods.

- Hens

6. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Originally Posted by hens91
Thanks for fast respond.

Enclosed is a new version of the dashboard - which may be of better use.
Do you have similar formulas for that view? Same INNDATA follows. Same principle of just copying formulas to next periods.

- Hens
I would love to get an answer for this post - if anyone might have it.

-Hens

7. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Try in B4:

=SUM(OFFSET('Inndata-Sheet1'!\$A5,0,MATCH(B\$2,'Inndata-Sheet1'!\$B\$3:\$S\$3,0),1,2))

8. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

you just need to think how much you need to move for each column, before to move 2 columns you needed to move 3 columns on the other sheet, hence *1.5, this time you need to move 3 columns for every 1 column. The formula are the same with this changes
B5 =SUM(OFFSET('Inndata-Sheet1'!\$B5,0,(COLUMN(A1)-1)*3,1,2))
b6 =OFFSET('Inndata-Sheet1'!\$D5,0,(COLUMN(A1)-1)*3)
B9 =SUM(OFFSET('Inndata-Sheet1'!\$B6,0,(COLUMN(A5)-1)*3,1,2))
B10 =OFFSET('Inndata-Sheet1'!\$D6,0,(COLUMN(A5)-1)*3)

its worth your time working out what the formula do
column is just a number A=1 b=2 c=3 etc
offset(starting cell, rows to move, columns to move,height, width)

9. ## Re: Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Originally Posted by davsth
you just need to think how much you need to move for each column, before to move 2 columns you needed to move 3 columns on the other sheet, hence *1.5, this time you need to move 3 columns for every 1 column. The formula are the same with this changes
B5 =SUM(OFFSET('Inndata-Sheet1'!\$B5,0,(COLUMN(A1)-1)*3,1,2))
b6 =OFFSET('Inndata-Sheet1'!\$D5,0,(COLUMN(A1)-1)*3)
B9 =SUM(OFFSET('Inndata-Sheet1'!\$B6,0,(COLUMN(A5)-1)*3,1,2))
B10 =OFFSET('Inndata-Sheet1'!\$D6,0,(COLUMN(A5)-1)*3)

its worth your time working out what the formula do
column is just a number A=1 b=2 c=3 etc
offset(starting cell, rows to move, columns to move,height, width)
Thanks a lot for the answer. Works perfect in test-sheet. I will sit down trying to write this in my actual sheet to understand the formula better.
Again, thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1