Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying formulas between files problem (Excel 2000)

    I have amended a tab in our workbook template. This tab has formulas that refer to another tab in the workbook named ANALYSIS. Each individual workbook has the an ANALYSIS tab. I need to distribute these amended formulas. When I copy-and-paste them from the template tab to another workbook, the formulas continue to reference the ANALYSIS tab in the template, not the ANALYSIS tab in the target workbook. I am reduced to turning the formula into a label, then copying, then editing out the ' to convert it back to a formula that correctly references just the ANALYSIS tab. This is a primitive and risky approach.
    Am I missing some setting or some option that pevents these formulas from pointing back to the source workbook? Or am I condemned to the primitive paste and edit approach? There are quite a few formulas spread out between ones that are not changing, and I tried ctrl+copy selecting separate cells, but they all pasted in rows on the target sheet.
    Any comments or confirmation would be appreciated. Thanks.

  2. #2
    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: Copying formulas between files problem (Excel 2000)

    It sounds like you might be copying and creating links to the other book.
    After you have copied them.
    Try going to edit - links
    <Change source>
    and browse to find the current file
    This should replace all the links to the other workbook to this wotkbook.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formulas between files problem (Excel 2000)

    That's a good ideat-thanks. But why should I have to do this? Why can't Excel just do a simple task like copying a formula without all the baggage and the assumptions? If I wanted a link, I'd paste a link or hyperlink. All I want is the lousy formula.
    The specific problem is that the end-users don't want to be bothered by such stuff. They 're not completely conversant with link management and I can't ask them to learn for this issue. Doing 25 workbooks myself would be a nuisance, too.

  4. #4
    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: Copying formulas between files problem (Excel 2000)

    <hr>Why can't Excel just do a simple task like copying a formula without all the baggage and the assumptions? If I wanted a link, I'd paste a link or hyperlink. All I want is the lousy formula<hr>

    Excel's response could be: if you didn't want a link, why did you create one?
    The problem is that you do have a formula with a link that you are copying and excel is copying the link. If you do not want the link, then don't add the sheet name: that is the link, It links to a sheet in a workbook. It is the same workbook so there is no external link.

    If you have a formula refer to a cell in that worksheet (without the sheet listed), there is no baggage since there is no link. If you add the sheetname (even if it refers to the same sheet) you are creating a link (workbook and worksheet). By creating the LINK to the other worksheet, you are adding the baggage. Excel "blindly" copies the link that you created. When you copy it into a different workbook, it becomes an external link because the link is to both a workbook and a woksheet.

    You could use an indirect formula instead of a direct and not have link:
    <pre>=indirect("'Sheet 2'!B1")</pre>

    can be copied to other workbooks without the direct link, though they are not "relative" and don't copy well to get other cells, so I don't know if that is any better.

    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
  •