+ Reply to Thread
Results 1 to 6 of 6

Copying sheets and referencing cells of copies - anyway to make this dragable/extendable?

  1. #1
    Registered User
    Join Date
    12-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    15

    Copying sheets and referencing cells of copies - anyway to make this dragable/extendable?

    Say you have sheets for specific items, and then an overview sheet which pulls some of the information for specific item sheets with simple references.
    Now let's say that to create a specific item sheet you just copy the previous sheet, so that they get named "Item", "Item (2)", "Item (3)", etc as is excel's default.

    Is there any way that you can write a reference so that you can then drag your cells across columns to update the reference formula to the next sheet copy automatically, without the use of vb code? (Ie, automatically update the formula from 'Item (2)'!CellNumber to 'Item (3)'!CellNumber)


    Answer: (Curtsy of TMShucks) is to use a format similar to =INDIRECT("'<sheetname> ("&COLUMN()<- any difference between cell number and sheet number>&")'!<Cell>")
    Last edited by spira; 12-09-2012 at 06:30 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Copying sheets and referencing cells of copies - anyway to make this dragable/extendab

    Possibly using INDIRECT and COLUMN().

    Would need to see sample workbook, data and formulae.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    12-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Copying sheets and referencing cells of copies - anyway to make this dragable/extendab

    Something like this:
    Sample.xls

    Though on a much larger scale.
    I need to be able to drag across the contents of column C into column D, and have the formulae update to reference the next sheet (ie, "Item (2)" -> "Item (3)" automatically).

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Copying sheets and referencing cells of copies - anyway to make this dragable/extendab

    In cell C1: =INDIRECT("'Item ("&COLUMN()-1&")'!B3") and drag across.

    If you rename the Item sheet as Item (1) you can also drag back for consistency

    You could do a similar thing with ROW() for the detail

    =INDIRECT("'Item ("&COLUMN()-1&")'!B" & ROW()+5 )


    Regards, TMS

  5. #5
    Registered User
    Join Date
    12-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Copying sheets and referencing cells of copies - anyway to make this dragable/extendab

    Awesome! Thanks so much TMS; I'd spent the last day wracking my brain to work that out.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Copying sheets and referencing cells of copies - anyway to make this dragable/extendab

    You're welcome. It's easy when you know, and gets easier with practice.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Copying sheets and referencing cells of copies - anyway to make this dragable/extendab

    Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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