+ Reply to Thread
Results 1 to 8 of 8

Copying data from 2 different sheets at once

  1. #1
    Registered User
    Join Date
    09-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question Copying data from 2 different sheets at once

    Hi,

    First part of my problem is that I have no proper description of it.

    Second part is this:
    I have data, which is income, the second column is a percentage of the total income. So far, so good. Problem is, I need to extend it to 50+ columns and have a fair amount of rows.
    Cell B1 and C1 are merged, and stand for a week, I do have the figures of each week and product but would like to see the percentage next to it.

    Quick example:
    A1 B1 C1
    income 1: =Sheet1!A2 =(B2/B$4)
    income 2: =Sheet1!A3 =(B3/B$4)
    Total: =sum(B2:B3)
    If I select B2:C3 and drag it into the next cells I have the problem that cell D2 becomes =Sheet1!C2 while I need it to be =Sheet1!B2

    Alternatively I tried it to do with 2 different sheets, where C2 and C3 are =Sheet2!A2 and =Sheet2!A3

    Thanks for the help.

    Chris
    Last edited by Appel; 09-05-2012 at 08:36 PM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copying data from 2 different sheets at once

    Do you have a sample file which you can upload?

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    09-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Copying data from 2 different sheets at once

    Ok thanks for the reply.

    I added an example, not the official document though. But the idea is the same

    So sheet 1 contains the data, and sheet 2 the output, including the percentages next to it, that is my ultimate goal.

    If I select B3 until C7 in sheet 2, I can drag it towards the next columns, however it skips a column every time I do, and leaving me with only 50% of the data.

    Hope this helps
    Attached Files Attached Files

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copying data from 2 different sheets at once

    Will this work?

    You keep your data as is in sheet1, then a macro inserts columns for you after every alternate column and puts in the %ages.

  5. #5
    Registered User
    Join Date
    09-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Copying data from 2 different sheets at once

    Thanks,

    I suppose that worked out.

  6. #6
    Registered User
    Join Date
    09-04-2012
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Copying data from 2 different sheets at once

    You can use the combination of the Address, Indirect, Row and Column functions to do what you want without having to mess around with a macro. Do note that this does have an impact on the calculation speed if you have thousands of rows (or columns) of data...

    Modified example file uploaded.
    Attached Files Attached Files
    Last edited by Cooldil; 09-06-2012 at 12:52 AM. Reason: missed editing row 7 on sheet 2 initially...

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copying data from 2 different sheets at once

    Appel,

    How did you get it solved? You did it manually?

  8. #8
    Registered User
    Join Date
    09-04-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Copying data from 2 different sheets at once

    To be honest, yes I did it manually.

    However, I did manage to get a macro work to insert a new column every next column. (used google and found an example, forgot where) And am sure I would have been able to fix it fully, given time.

    It is mainly because of my lack of skills yet, and the number of columns werent that big. And since I was running out of time.... I did it manually. I know that it normally pays off to invest the few hours into making the macro, especially for future use.

    I might make a macro in the future, since I might want to use it more often.

    Thanks for the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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