+ Reply to Thread
Results 1 to 6 of 6

Automatically copying data from one sheet to another

  1. 03-14-2017, 08:06 AM #1
    fionafiona
    fionafiona is offline
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

    Automatically copying data from one sheet to another

    Hello!

    I have a question which is probably relatively simple...

    So I have various data sets which corresponds to a specific item (1, 2, 3, ... ) and within each item there N data points (which have a limit of N = 600) [each item has 10 repeats]. In many cases (such as this) the number of data points is less than 600. Is there a way to link the item number to the data points so that when I input data into the 'raw data' sheet they automatically fill the 'master' sheet? I've attached some example data and would be grateful for some advice!

    Regards,

    Fiona
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-14-2017, 11:57 AM #2
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,562

    Re: Automatically copying data from one sheet to another

    If I understand correctly, the following array entered formula* can be used:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    *1) Select cell C2 on the Master sheet,
    2) paste the formula into the formula bar,
    3) activate the formula by simultaneously pressing the Ctrl, Shift and Enter keys.
    You will then be able to copy across to L2 and while the range C2:L2 is selected double click the fill handle of cell L2 to have Excel copy the formula down to row 601.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  3. 03-14-2017, 04:17 PM #3
    fionafiona
    fionafiona is offline
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

    Re: Automatically copying data from one sheet to another

    Hello, thank you so much for a reply - I really appreciate it.

    I do not think this formula would work in this case - I forgot to mention that all the 'ITEM NO.' columns are directly below each other such that using C2:C601 as would not work because this would extend onto data for ITEM NO. = 2. I've attached another example of what I mean.

    Thanks again!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 03-14-2017, 04:26 PM #4
    fionafiona
    fionafiona is offline
    Registered User
    Join Date
    03-10-2017
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    11

    Re: Automatically copying data from one sheet to another

    Sorry, there was a mistake in the sheet I sent you - I have corrected it.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-14-2017, 09:54 PM #5
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,562

    Re: Automatically copying data from one sheet to another

    If I understand correctly when there is no data in column C that is the signal that it is time to restart the data points and start the next item number. If that understanding is correct then I think the following will work, however with as much data as you are describing I would imagine that it will take a long time, and may well necessitate changing the calculation mode to manual. Also with that in mind I omitted the usual functions that would be used to 'tidy up' the data in an effort to let the formulas work as efficiently as I felt was possible.
    In an effort to illustrate how this works I further reduced size of the raw data and master sheets (RD 1 and MS 1)
    A helper table is created in columns O:Q which could be moved to columns XFB:XFD or hidden for aesthetic purposes.
    1's are placed in A3:B3,
    The rest of column A is populated by the formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    The rest of column B is populated by the formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    The array entered formula* (see activation steps in post #2) that populates C2:L40 is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-15-2017, 04:31 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Automatically copying data from one sheet to another

    I'm not clear what your expected results are meant to look like. Can you populate some of the cells manually so that we can see what you want?
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    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. Automatically copying data from one sheet to another
    By abshall in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2014, 10:47 AM
  2. [SOLVED] Automatically copying data from one sheet to another.
    By scokaw in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-18-2013, 01:56 AM
  3. Copying Data from one sheet to another automatically
    By patrickdjames in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-28-2013, 10:37 AM
  4. copying dynamic data from main sheet to relative sheet automatically.
    By SHEIKH TALIB in forum Excel General
    Replies: 0
    Last Post: 03-04-2013, 03:25 PM
  5. Automatically copying data to a second sheet
    By carslake in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-14-2013, 12:39 PM
  6. copying data automatically from one or more cells to another sheet
    By andylowny in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2012, 05:29 AM
  7. Copying daily data to one sheet to another automatically
    By amqazi12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2012, 03:33 PM
  8. copying filtered data from one sheet to another automatically
    By hnnn in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-14-2010, 10:36 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1