+ Reply to Thread
Results 1 to 7 of 7

Copy a table of formulas andmaintain pattern of data extraction - please can someone help?

  1. #1
    Registered User
    Join Date
    08-05-2016
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    18

    Copy a table of formulas andmaintain pattern of data extraction - please can someone help?

    Hi all.

    Please could someone help make my life a little easier? I have a data source that I need to repackage into a table. Both the data source and the table are on separate tabs in the attached excel. The first table has formulas in it that guide it to the correct cell to get the data. However, I have multiple tables that need to be sequentially populated with the data in the next row in the data source (i.e. the first table contains A, B, C, etc; the second table contains A2, B2, C2, etc….). I tried to copy the formulas into the next table by dragging the bottom right corner of the first table, but instead of extracting the data from the next row in tab data source, it pulls data from 14 rows deeper (because 14 is the number of rows in the copied table). How would I fix this?

    Thanks in advance, excel masters.

    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Copy a table of formulas andmaintain pattern of data extraction - please can someone h

    Hi,

    Give this a try in cell A2.

    Please Login or Register  to view this content.
    Hope this is helpful.

    Cheers

  3. #3
    Registered User
    Join Date
    08-05-2016
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    18

    Re: Copy a table of formulas andmaintain pattern of data extraction - please can someone h

    This works great, thank you southward. I need to go read this about this offset formula!

    Also, do you know how I would edit the formula to say "if field is blank, get data from the cell to the right instead? No worries if not, you have more than helped with the above formula!

    Kind Regards.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,327

    Re: Copy a table of formulas andmaintain pattern of data extraction - please can someone h

    Note of caution before you get too excited: the OFFSET function is quite a resource hog if overused and can slow calculations down quite a bit, making a workbook sluggish. Use sparingly!
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Copy a table of formulas andmaintain pattern of data extraction - please can someone h

    Hi,

    Here is a modification of the formula.
    If the cell it is looking to pull data from is blank then it will pull data from one cell to the right.

    Please Login or Register  to view this content.
    Cheers

  6. #6
    Registered User
    Join Date
    08-05-2016
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    18

    Re: Copy a table of formulas andmaintain pattern of data extraction - please can someone h

    Cheers for this, Southward. All really helpful - thank you a bunch!

  7. #7
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Copy a table of formulas andmaintain pattern of data extraction - please can someone h

    Glad I could be of some assistance

+ 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 Formulas from 'Formulas' Sheet into Data Table Columns
    By Jonny757 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-10-2020, 08:18 PM
  2. OCR Data extraction with VBA from PDF copy
    By punna111 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-06-2013, 02:21 AM
  3. [SOLVED] Complex data extraction from a table
    By Corke in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-14-2013, 11:49 AM
  4. [SOLVED] Excel 2007 : copy data in a pattern
    By gjmptw in forum Excel General
    Replies: 2
    Last Post: 03-26-2012, 02:25 AM
  5. Excel 2007 : Extraction of values following a pattern
    By gvaltat in forum Excel General
    Replies: 14
    Last Post: 11-03-2011, 09:46 PM
  6. Replies: 4
    Last Post: 02-23-2011, 12:51 PM
  7. Replies: 9
    Last Post: 05-08-2010, 11:49 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1