Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Links Frustration (2002/2003)

    I have two sheets that are linked. For some reason, I have a continual problem not have the link take effect -- meaning, the data isn't updated on the sheet that's linked and pointing to the other sheet. I use a fair amount of INDIRECT formulas -- could that be a problem? Sometimes, if I totally close Excel, reopen it, and start again, it works. Other times it doesn't. I've tried this on a machine running 2002 and one running 2003 and have the same problem.

    Thanks in advance for any suggestions.

  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: Links Frustration (2002/2003)

    Do you have calculations on automatic or manual?

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Links Frustration (2002/2003)

    Automatic

  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: Links Frustration (2002/2003)

    Do any of the indirect links access "closed" workbooks? The Indirect function does not work with closed workbooks.

    Also, could you elaborate on when it is not updating? when the value in the indirect link changes, when a formula feeding the indirect formula changes or what? What are the formulas that do not seem to update?

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Links Frustration (2002/2003)

    The application is for a builder. One workbook (the "master" workbook) contains a sheet with a list of change orders for a given job. The other workbook has a sheet that uses a sub-set of those change orders that are pulled from the master based on an identification number or name for the change orders.

    So, there's a formula that uses INDIRECT (because it checks to see what master file is being use for a given job) based on the master workbook name.
    Something like: INDIRECT("'["&$D$1&"]ChgOrderLog'!$G"&ROW()-4) This formula that determines which master file, pulls from the specific sheet, the G column and various rows.

    Also, the sheet is protected so the user can't trash formulas...but, even when I've unprotected it, resaved, closed, reopened, I still don't get the links to work. Again, all of a sudden they'll magically work...and I can't isolate anything specific that I'm doing that makes that happen. Just once in awhile after the workbooks are closed and then reopened, it works...but not always. Weird. And driving me totally nuts (or nuttier than I am already).

  6. #6
    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: Links Frustration (2002/2003)

    Are both files opened when you have the problem or is only 1 opened? Both should be opened to update - Indirect does not work looking at closed workbooks.

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Links Frustration (2002/2003)

    Yup...both files are open...Even if they're not, when I use the Edit|Links option, I open the source at that time and click on the update button as well.
    Nothing.

  8. #8
    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: Links Frustration (2002/2003)

    The INDIRECT function (even with external workbooks) does not create links. If D1 does not contain the name of an open workbook the indirect will not work. The filename "linked" with INDIRECT is not (but may be) always in the Edit-links. To be in the Edit-links you must also have "direct links" to the workbook.

    Another option is that perhaps the problem is with the "direct links" and not the indirect function themselves. What links do you have in the workbook?

    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
  •