+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    09-20-2017
    Location
    Oslo, Norway
    MS-Off Ver
    10
    Posts
    5

    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. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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. #3
    Registered User
    Join Date
    09-20-2017
    Location
    Oslo, Norway
    MS-Off Ver
    10
    Posts
    5

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

    I have now enclosed an example, with comment.

    Do not hesitate if more information are needed.

    - Hens
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    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. #5
    Registered User
    Join Date
    09-20-2017
    Location
    Oslo, Norway
    MS-Off Ver
    10
    Posts
    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.

    Thanks in advance.

    - Hens
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-20-2017
    Location
    Oslo, Norway
    MS-Off Ver
    10
    Posts
    5

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

    Quote Originally Posted by hens91 View Post
    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

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    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. #8
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    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. #9
    Registered User
    Join Date
    09-20-2017
    Location
    Oslo, Norway
    MS-Off Ver
    10
    Posts
    5

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

    Quote Originally Posted by davsth View Post
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy formula down column but need to skip 'n' rows
    By wschultek in forum Excel General
    Replies: 18
    Last Post: 03-12-2014, 03:24 PM
  2. Replies: 3
    Last Post: 01-30-2013, 04:32 PM
  3. Help with formula that will copy when blank but reset/skip when not.
    By LightingPop in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 11-13-2012, 09:29 PM
  4. copy a formula down and skip n cells
    By sonordrummer in forum Excel General
    Replies: 2
    Last Post: 10-10-2012, 02:08 AM
  5. Excel 2007 : Copy/Drag Formula's - Need to Skip Rows
    By Rapacious in forum Excel General
    Replies: 3
    Last Post: 03-01-2012, 06:10 PM
  6. Copy Formula Horizontally Need to Skip Column
    By samchargers09 in forum Excel General
    Replies: 7
    Last Post: 12-22-2010, 06:02 PM
  7. Replies: 6
    Last Post: 08-30-2010, 01:09 AM
  8. [SOLVED] copy paste formula to skip rows
    By Greg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-08-2006, 05:42 PM

Bookmarks

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