Consulting

Results 1 to 2 of 2

Thread: Excel Help Please!!! Making Duplicate Spreadsheets

  1. #1

    Excel Help Please!!! Making Duplicate Spreadsheets

    Hi,

    This is a difficult one to explain, but I'll try my best... I'm sure that the fix is very easy - I'm just not that great with IT!

    I have 2 spreadsheets (files) Let's call them A2013 and B2013
    These spreadsheets talk to each other (B2013 picks up data that is entered into A2013)

    I want to make 2 new sheets for next year (2014) that are exactly the same. Let's call them A2014 and B2014.

    How do I make duplicates from A2013 and B2013 so that A2014 and B2014 talk to each other in the same way? i.e. so that the formulas copy over but update to recognize the new 2014 sheet.

    I hope that this makes sense.

    Thank you very much for your help!

    James

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Use INDIRECT

    For example

    A2013 cell A5 - =INDIRECT("B"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+2,99)&"!A5")

    The MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+2,99) gets the year number from the sheet name
    Then append !cell to it
    And pass the concatenated string to INDIRECT which indirects into that cell.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •