Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove links in formula from extermal ref. (2000/9

    I have a file which at some point I must have been working off a floppy and the file at the same time. The formulas now have an external reference to the file on the floppy, thus creating a box when opening, asking if you want to refresh.

    Does anyone know how I can get rid of this reference? I have tried to highlight it and press F9 (which is supposed to replace it with a value according to Microsoft) but it still wants to look for the file so I get a #Ref.

    =((0.9^(LOG($U4)/LOG(2)))/(0.9^(LOG('A:[Schedule P-LC Example.xls]Schedule P listing'!$G106)/LOG(2))))*'A:[Schedule P-LC Example.xls]Schedule P listing'!J106

    This is how the formula looks. The problem area being in the reference to A:

    I do not have the floppy, nor the original file that it was pulled from. The file has been resurrected from a couple years ago!

    Also, I am not a power user of Excel (someone else created the formulas) so if you can answer with "Laymans" terms/ explanations that would be great!

    Thank you in advance.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Remove links in formula from extermal ref. (2000/9

    Under the Edit | Links dialog, XP & 2003 have a Break Links option that will replace the link with the last resulting Value. If this option isn't in 2000, you can copy the cell Value and then use Edit | Paste Special | Values | OK. If there are a number of them you can Steve's macro <!post=here,394839>here<!/post>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Remove links in formula from extermal ref. (2000/9

    Open the workbook and reply No to the prompt if you want to refresh the links.
    Select Edit | Links...
    Select all items in the list box.
    Click Break Link, and confirm.
    Close the dialog.

  4. #4
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove links in formula from extermal ref. (20

    Thanks for the answers. Unfortunately there is no Break Links under Excel 2000. I found that odd when I was originally trying to break them. I can't change it to values because the rest of the formula is used to make other calculations based on the number entered into a different cell elsewhere in the spreadshett. It hink what I might do is find someone with a newer version of excel, have them break the links for me and then it will be done.
    Thanks again

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Remove links in formula from extermal ref. (20

    See if Bill Manville's free utility FindLink will replace the references with their values for you - download it from Office Automation Ltd. - The Excel MVP Page.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Remove links in formula from extermal ref. (20

    In a quick test, Break Links converts the entire formula to it's value, not the linked components of the formula, so it won't help you with the problem. Because you have two separate links embedded in your formula, you're not going to easily be able to reverse engineer the value of the link (if at all). You could try to reverse engineer approximate the fixed values in 'A:[Schedule P-LC Example.xls]Schedule P listing'!$G106 and 'A:[Schedule P-LC Example.xls]Schedule P listing'!J106 by creating a formula:

    =((0.9^(LOG($U4)/LOG(2)))/(0.9^(LOG(G1)/LOG(2))))*H1

    and entering likely values into G1 and H1 until it matches the value in the linked formula. If you were working off the floppy, you may have some idea what reasonable values those two cell references could be.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Remove links in formula from extermal ref. (20

    Have you tried the macro that John referenced (Re: Changing links to values (Excel 2000))

    This will convert the links to values. If it is opened with the links not updated, it would do the equivalent of <F2><F9> though would bypass excel's attempt to recalc by looking it up (which gives you the error) and just replaces the value.

    You could add other "conditions" if you did not want to convert all the links.

    Steve

  8. #8
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove links in formula from extermal ref. (20

    Hi, I'm back. I'm not sure how to do the Macro, but if I did it right, it changed the whole thing to one value. What I need is to keep the rest of the formula and only convert the part that is linking to the A: disk to a value. I am attaching a sample of the sheet this time. ( I should have done this the first time).
    Col. A - Pink - changeable
    Cols. M, P, S (Yellow) - changeable
    Cols: W, X, AB, AND AD - (Green) Problem area with links to A: disc

    If you change the values in the Pink or Yellow, you will note that the values in Green change. For this reason, I can't convert to a Value in the entire cell. If Yellow is 0 the express #NUM is in the cells as it is all dependent on the values in the other areas.

    The sheet is used to get prices determined on different qty and item each values, therefore none of the Green items is a constant that I can just replace. I do NOT have the original file! or I could find the information and replace whatever it is looking for at least to the same spreadsheet.

    Thanks again,
    Heather

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Remove links in formula from extermal ref. (20

    The best you can probably do is give the workbook to somebody with Excel 2002 or later and ask him/her to break the links for you. If the workbook does not contain sensitive data and can be zipped to less than 100 KB, you can attach it to a reply and someone were will break the links.

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Remove links in formula from extermal ref. (20

    Hans, and Steve, the OP <!profile=hlstew>hlstew<!/profile> has TWO embedded links in his/her formulas, and wants to obtain the values of the broken links, while retaining the rest of the formula.

    =((0.9^(LOG($U4)/LOG(2)))/(0.9^(LOG('A:[Schedule P-LC Example.xls]Schedule P listing'!$G106)/LOG(2))))*'A:[Schedule P-LC Example.xls]Schedule P listing'!J106

    I don't know how the OP can do what he wants, and I don't think what we have been telling him about breaking links or using paste values or Steve's macro works for what he wants. All the tools that I'm aware of break the link by replacing the entire cell result with its value. Maybe one of you knows how to obtain the values in the two embedded links, but I don't. (I hope this explains what I was rambling about in my earlier <post#=450661>post 450661</post#>.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Remove links in formula from extermal ref. (20

    John,

    You're correct, all unlink methods replace the entire formula with its result. Since the formulas depend on two linked cells, there is no way to reconstruct the original values from the resulting cell value. This is unfortunate for hlstew, but there doesn't seem to be a solution to his problem.

  12. #12
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove links in formula from extermal ref. (20

    Well I feel a bit better about not being able to do it, if you guys are all stumped.
    Maybe we will have to recreate that obscene formula from scratch. Better yet I will just tell everyone to click "no" when it opens and get on with their day. :-)
    Thanks again for all the help.
    Heather

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Remove links in formula from extermal ref. (20

    The formula is not the issue. We could convert it to a formula easily enough. It is knowing what the 2 values from the other workbooks are that we can not do. To recreate the formula, you need the 2 values from the other workbooks.

    As I mentioned, given the value that you have and either one of them, you could calculate the other one...

    Excel stores the "cell's value", it does not store those individual values from the external link.
    Steve

Posting Permissions

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