1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

How to reference another sheet Vertically to Horizontally

Discussion in 'Business Applications' started by wade.clegg, Jan 9, 2018.

Thread Status:
Not open for further replies.
Advertisement
  1. wade.clegg

    wade.clegg Thread Starter

    Joined:
    Mar 8, 2017
    Messages:
    11
    I am trying to reference another sheet's summed data =sum(B368:B374) that has data that runs Vertically and have the data run Horizontally on B1, then I want to drag from B1 to C1 having C1 =sum(B375:B381).

    I have been doing each calendar week individually and it can be tedious and time consuming. Below is a picture of what I am trying to achieve.

    From:
    upload_2018-1-9_9-45-21.png

    To:
    upload_2018-1-9_9-45-41.png
     

    Attached Files:

  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Copy and Paste Special "Transpose" may accomplish something like what your looking for?
     
  3. wade.clegg

    wade.clegg Thread Starter

    Joined:
    Mar 8, 2017
    Messages:
    11
    I tried this but it does the following: B1=sum(B368:B374) C1=sum(B369:B375) only moving one row down instead of the whole week. My goal is B1=sum(B368:B374) C1=sum(B375:B381).
     
  4. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Without and example to work from I'm just guessing.
     
  5. wade.clegg

    wade.clegg Thread Starter

    Joined:
    Mar 8, 2017
    Messages:
    11
    OK best I can do is explain what I've done so far to get to as far as I am.. I think I'm close..
    =SUM(G375:G381)
    =SUM(G382:G388)
    =SUM(G389:G395)
    At this point I highlight all three sums and the 6 blanks in between each to represent the calendar week. Drag it down to fill in the rest of the year and check to make sure that they all are correct.
    ....
    Last line =SUM(G718:G724)

    I do all this off to the side and get 52 numbers (1 for each week as a sum). Highlight all and press F5. When the window pops up, select special, click blanks and hit OK. This selects all the blanks in between my numbers. Right click, delete, move up. Result:
    20,800
    15,600
    10,400
    ....
    Last line =10,800

    Now what I want to do is right click copy, right click paste as Transpose; however, it is changing my target so I have to insert an absolute on the group of selected cells.. stuck here at this time.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1202604

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice