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

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.

I have now enclosed an example, with comment.

Do not hesitate if more information are needed.

- Hens

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,))

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.

Thanks in advance.

- Hens

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.

Thanks in advance.

- Hens

I would love to get an answer for this post - if anyone might have it.
Thanks in advance!

-Hens

Try in B4:

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

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)

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!