+ Reply to Thread
Results 1 to 4 of 4

Copying referenced data blocks

  1. #1
    Forum Contributor
    Join Date
    02-18-2013
    Location
    Berlin, Germany
    MS-Off Ver
    Excel 2016
    Posts
    221

    Copying referenced data blocks

    Hello,

    I need some help tweaking the attached workbook.

    In the sheet "sample" you have a master worksheet which is getting data from other sheets, which are named if the format "DD.MM.", where "DD" stands for the day and "MM" for the month the data was generated on. I did not include these data sheets, because they are not relevant for what I want to achieve. That's why you see all the reference errors in the sample.

    The sheet, works like this: If I would add a data sheet today, I would name it "15.10." and some of data in this sheet would be copied in the "sample sheet" in C35:H35.

    Do you have any ideas to do the following:

    1) After real data (and not just the reference formula) is in C35:H35, I want the number in cell I102 to be set to "52". This is the same number as in cell A35, next to the block C35:H35.

    2) Then, I want the data in K103:P103 to be copied as numbers in R35:X35. This is the block next to the number 52 in Q35. The block K103:P103 changes every time after a new data sheet comes in. For instance tomorrow, we would have data in C36:H36 and this would change the numbers in K103:P103. The new numbers should be then copied in R36:X36, next to 51 in Q36.

    Is there a simple way to get this done? Thank you for your help!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Copying referenced data blocks

    Perhaps the following will work (harder to test because of the #REF! errors).
    1. For cell I102: =INDEX(A4:A87,SUMPRODUCT(--(C4:C87<>"")))
    2. For cell K102*: =INDEX(C4:C87,MATCH($I102,$A4:$A87,0))
    *Drag the fill handle across to column P.
    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.

  3. #3
    Forum Contributor
    Join Date
    02-18-2013
    Location
    Berlin, Germany
    MS-Off Ver
    Excel 2016
    Posts
    221

    Re: Copying referenced data blocks

    Thank you very much!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Copying referenced data blocks

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. [SOLVED] Copying formula where one referenced cell must remain constant
    By Punxatawny in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2017, 04:27 PM
  2. [SOLVED] Grouped Data Blocks editted to Columns of Data based off left cell value?
    By BlazzedTroll in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 06-10-2013, 03:06 PM
  3. Replies: 2
    Last Post: 01-21-2013, 02:35 AM
  4. Using macros to identify blocks of data and summarise those blocks
    By gophbeav in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2012, 04:35 AM
  5. copying building blocks
    By Peeps32 in forum Word Formatting & General
    Replies: 3
    Last Post: 11-11-2011, 01:23 PM
  6. Copying blocks of rows into new worksheets
    By eonizuka in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-01-2009, 04:09 PM
  7. copying two referenced sheets
    By bjkent13 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-03-2008, 04:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1