Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    --------------------------------------------------------------------------------

    I have a complex model with several interlinked worksheets which automatically pull data between sheets.

    I needed to use one of these standard sheets as the basis of another sub-set of data, so I made a copy the original sheet within my model (using Edit, Move or Copy Sheet).

    Then, working on my copy, I deleted several rows and columns so I was just left with the data I needed for this one-off exercise. Once I had deleted rows and columns, the only data left in my copy sheet was actual numbers and words - ie there were no links or formulas referring to other worksheets in my model, left in my copy worksheet.

    I then decided to move the copy worksheeet out of my complex model into a more appropriate workbook, which I did with the Edit, Move or Copy Sheet function.

    I then saved and closed both files.

    Now, when I open my original model, I get the "The workbook you opened contains automatic links to information in another workbook etc" message.

    When I click on Edit, Links, the source file is identified as the file I moved the new sheets into.

    So, my question is:-

    Having moved a sheet to another file, even if that sheet contains no formulas or references back to cells in the workbook from which it came, is that then classified as an "automatic link"?

    or

    Is it actually the case that although I am certain that the sheet I moved contains no formulas or references back to the original model, actually I am wrong, and it does.

    Obviously my aim is to get rid of that (erroneous?) message....!

    Thanks

    Neil

    NB. When I open the file into which I moved the worksheet (without the original model being open), I don't get the message.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Have you checked the defined names to see if any of those point to the moved sheet?

    Note: it would also be courteous to mention the cross-posting.

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for that Rory.

    I thought you might be on to something, but I've checked my named ranges, and there are none in the destination file, so I don't think that is it.

    I've taken a while to work out what you meant by your second sentence, but I have just tumbled to it - so yes, I acknowledge that I have posted the same query on another forum, and I haven not had an answer from that one or this (which is unheard of in all the questions I have ever posted!).

    So, apologies for not pointing that out.

    But I do still need some help with this. I wonder if, perhaps, my verbose, over complicated opening post is putting people off?

    Let me re-phrase my question in a new, simpler way, describing a hypothetical situation:-

    Say I open a file and enter data into a worksheet, and then make a copy of that worksheet within that file (so there are 2 worksheets, both containing data, but with no formulas or links looking up or pulling data between the 2 worksheets).

    I then use the move function, to move (not copy) one of these worksheets from that source file, into a new destination file. I then save and close both files.

    If I then open up my original source file (which now has just one worksheet) should I expect to receive the "The workbook you opened contains automatic links to information in another workbook etc" message?

    ie. Does Excel presume a link still exists between separate files where a worksheet has been moved from one to the other, even when that sheet contains no linking formulas?

    Many thanks in anticipation.

    Neil

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just to check - which workbook did you check the defined names in?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I actually checked it in both.

    The only defined names in the destination file were print areas, and I just deleted these.

    There were no defined names in the original source worksheets within the source file.

    Neil

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The only way you should get a link is if there are defined names, or if the sheet is referred to in other formulas (including chart formulas). Any hidden names? (you can see them if you have JKP's Name Manager)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Okay - thank you for that, Rory.

    I obviously need to re-check my files for links, as there is obviously something in there.

    Can you tell (from the info I have given) if it is my source file which I need to be looking for links in?

    (I won't now get chance to look at this until next week, but I really appreciate your input. I'll report back when / if I solve it!).

    Have a good weekend.

    Neil

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You need to check the original model as something in that seems to be pointing to the other file. You could do a search for Bill Manville's FindLink add-in which may help you.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Well Rory - you (and Mr Manville) have cracked it! What a brilliant utility.

    There were buried links in an obscure page in the original model which I haven't used for months. No idea how they got there, but FindLink did what is says on the tin, and soon tracked them down.

    Most importantly I now understand better what prompts the Excel "The workbook you opened contains automatic links...etc" message to appear, so that has been as useful as resolving this particular problem.

    Many thanks for your help.

    Neil

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Glad to help.

    Would you mind just posting the solution on MrE (if you haven't already) for the benefit of others?

    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Solution now posted on Mr E, Rory.

    Cheers

    Neil

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Much obliged!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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