+ Reply to Thread
Results 1 to 3 of 3

Copy or reference data to merged cells

Hybrid View

  1. #1
    Registered User
    Join Date
    06-20-2009
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Copy or reference data to merged cells

    So I have a sheet with data (data sheet). Each rows is a data points and the columns are metadata for each point.

    I want to split the data into 4 rows in another sheet (schedule sheet)so I can see the metadata across a timeline. EG, choose a data point to occur in a particular quarter and show metadata in the three rows below.

    I've tried merging rows in the schedule sheet and copy and paste data from data sheet, Excel doesn't allow copy into merged cells. I've also tried using cell references, but when copying references down the references jump by 4 rows.

    In the attached example, schedule sheet correctly reads A3, A4 & A5 when manually entered into cells A5, A9 & A13 respectively.

    When copying formula down from A13, the formula starts to jump 4 references
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Copy or reference data to merged cells

    hmm merge cells are problematic and should be avoided when possible
    to answer your query...have you considered approaching it from a different direction?

    essentially you just want the table into expanded table correct?

    why not use the 1-18 as the primary (lookup value) and just vlookup the rest of the details in instead of referencing?

    ps the Right() bit is not required but since the columns were identifiable from the headers i took the liberty of using it
    Attached Files Attached Files
    Last edited by humdingaling; 11-28-2013 at 10:01 PM. Reason: additional info
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Copy or reference data to merged cells

    if you want to fill the merged cells then in a5
    =INDEX('data sheet'!A:A,CEILING(ROWS($A$1:A9)/4,1)) filled down and across
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ 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] Reference Cells into Merged Cells
    By Family Man in forum Excel General
    Replies: 6
    Last Post: 11-12-2013, 12:15 PM
  2. [SOLVED] Reference or Vlookup against merged cells
    By Deventus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-13-2012, 11:48 AM
  3. [SOLVED] Copy Merged cells
    By diptis in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-24-2012, 11:39 AM
  4. Copy data from a sheet and Paste in another sheet into Merged cells
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-10-2011, 02:01 PM
  5. Reference to Merged Cells
    By excelhelp18 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-29-2008, 10:49 AM

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