Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2015
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Problems moving Excel control sheet

    I have spent a lot of time creating a control sheet for my accounts for 2014. The sheet accumulates all the different figures from the 12 months of the year. So, a typical Cell Formula could be;

    =SUM(April!F3;April!F4;April!F5;April!F6)

    The problem I have is moving this sheet into a NEW workbook for 2015, when I try to move or copy the control sheet it then refers back to the original control sheet like this;

    =SUM('[Accounts USA 2015.xlsx]April'!F3;'[Accounts USA 2015.xlsx]April'!F4;'[Accounts USA 2015.xlsx]April'!F5;'[Accounts USA 2015.xlsx]April'!F6)

    How do I transfer the sheet without all the references and with only the original formula;

    =SUM(April!F3;April!F4;April!F5;April!F6)

    Any help would be appreciated.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    You could check if the file is 'linked' to the original file which had the sheet.
    If so, you need to change the link to point to the current workbook.
    This will remove the formula references to the previous file, provided the current file has the same sheet tab names as the previous file:

    So in the new 2015 workbook, select the top-panel Ribbon tab for Data.
    In the 2nd group ,labelled Connections, select the option Edit Links
    In the Edit links dialog box, click the button labelled [Change Source..]
    Then browse to the current filename.
    If the links 'disappear', you have updated the file OK.
    Click the [Close] button on the Edit Links dialog box.

    Then save the file

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    RaezoR (2015-05-09)

  4. #3
    New Lounger
    Join Date
    Feb 2015
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Awesome! It worked, many thanks, I really appreciate it. Have a great weekend!

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Raezo.

    Or you could just save the workbook as "new name" and then delete the data you didn't want. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

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