+ Reply to Thread
Results 1 to 9 of 9

copy data from one sheet to another

  1. 07-14-2016, 04:58 AM #1
    John Sio
    John Sio is offline
    Forum Contributor
    Join Date
    07-26-2013
    Location
    Athens, Greece (born and raised in Sydney, Australia)
    MS-Off Ver
    Excel 2010
    Posts
    114

    copy data from one sheet to another

    Hi guys

    I need to do the following and need a formula etc

    I have some data on sheet 2 from A1 to J30. The data in Column A does not change and is always as follows, A1 = 1, A2 = 2, right down to A30 = 30. Columns B1 to J30, the data changes all the time.

    I need to be able to be in sheet1 and type in any number between 1 and 30 in a cell and this will bring from sheet 2 the info from that row.

    For example, if I type the number 9 then the information in sheet2, A9 through to J9 will be copied over to sheet1, ie alongside where I typed in the number 9.

    Can anyone help?

    Thanks

    John
    Reply With QuoteRegister To Reply
  2. 07-14-2016, 05:03 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,518

    Re: copy data from one sheet to another

    Let's say you type 1 into A1 of sheet 1. In B1, type:

    =VLOOKUP(A1,Sheet2!$A$1:$J$30,2,FALSE)

    In C1, type:

    =VLOOKUP(A1,Sheet2!$A$1:$J$30,3,FALSE)

    And so on to J1, incrementing the number in red by one each time.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-14-2016, 05:05 AM #3
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,770

    Re: copy data from one sheet to another

    Try

    in B2 and drag across columns

    =INDEX(Sheet2!$A$1:$I$30,$A2,COLUMNS($A:A))

    A2= 1 to 30

    or modifying Ali's formula:

    =VLOOKUP($A2,Sheet2!$A$1:$I$30,COLUMNS($A:A),0)

    Drag across
    Last edited by JohnTopley; 07-14-2016 at 05:22 AM.
    Reply With QuoteRegister To Reply
  4. 07-14-2016, 05:52 AM #4
    John Sio
    John Sio is offline
    Forum Contributor
    Join Date
    07-26-2013
    Location
    Athens, Greece (born and raised in Sydney, Australia)
    MS-Off Ver
    Excel 2010
    Posts
    114
    Wow, thanks guys!

    Something a little more simple which eludes me at the moment.

    How do I save the info brought over on sheet 1, into a permanent value?

    Apologies about the simplicity

    Thanks again

    John
    Reply With QuoteRegister To Reply
  5. 07-14-2016, 05:54 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,518

    Re: copy data from one sheet to another

    You can't, but that's not what you asked!

    (Well, you can, but doing so would overwrite the formulae.)

    You should restate your objectives in their entirety, as the advice offered will differ accordingly.
    Reply With QuoteRegister To Reply
  6. 07-14-2016, 06:04 AM #6
    John Sio
    John Sio is offline
    Forum Contributor
    Join Date
    07-26-2013
    Location
    Athens, Greece (born and raised in Sydney, Australia)
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: copy data from one sheet to another

    Ok

    Once the values have been brought over into sheet1, I don't need the formula to be available for those values and want them to become a permanent value on that sheet.

    Is that more clear?
    Reply With QuoteRegister To Reply
  7. 07-14-2016, 06:10 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,518

    Re: copy data from one sheet to another

    Yes, but what then? Is this simply a one-off transaction, or are you going to want to do it again, perhaps on another sheet? I'm not at all clear what the ultimate objective is - after all, you could just copy and paste that row. What are you trying to automate here and for what reason? Do you see what I'm getting at?

    If you want to make the row permanent, and it is just a one-off, then select the row, copy it and paste special (values) over the top.
    Reply With QuoteRegister To Reply
  8. 07-14-2016, 06:22 AM #8
    John Sio
    John Sio is offline
    Forum Contributor
    Join Date
    07-26-2013
    Location
    Athens, Greece (born and raised in Sydney, Australia)
    MS-Off Ver
    Excel 2010
    Posts
    114
    Ok, that's what I was looking for. Thanks for your help.
    Reply With QuoteRegister To Reply
  9. 07-14-2016, 06:25 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,518

    Re: copy data from one sheet to another

    No worries. :-)

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy data from one sheet to another sheet multiple times based on criteria from one sheet
    By manojuasc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2016, 01:16 PM
  2. copy data to another sheet,filter it there,sort it,then copy it again onto another sheet
    By 4evra in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2013, 07:03 AM
  3. backup odbc data pull table from one sheet and copy/paste to another sheet to trend data
    By marabm75 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-21-2013, 03:09 PM
  4. Copy Data from a fixed form of data sheet into a master excel sheet.....
    By sirimhk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-18-2012, 08:48 AM
  5. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM
  6. Copy Data from Multiple weekly Sheet to Monthly sheet and filter the data
    By santhoshjoseph in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2012, 07:07 AM
  7. Copy Sheet / Create & Name New Sheet / Insert Before a Sheet / Paste Data
    By thinkspac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2012, 02:27 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1