Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Broken Link in workbooks (Excel 2000)

    Hi all,
    Got a poser here. I have a student who manages a very large database in Excel. He has a major problem in the Final tally workbook (the one that Grand totals figures from 12 other workbooks (monthly totals, one for each month of the year). These folders (each hold a seperate file) exist on a server, and his formula on the final tally sheet is about a mile long in each cell, because it looks a bit like this:
    =[Folder on Server], [FileName of file in folder on server],[SheetName of File in folder]+etc...12 more additions. It really is too long for me to remember how it goes exactly, but I hope you get my drift. Well, anyway...Each one of the folders on the server were renamed to correlate to the year the formula was first entered, eg. October 1999, September 1999...and have since been renamed to October 2002, September 2002...Well, now the formulas do not work in the final Grand totals file like it used to. What happens is when someone clicks on it to update the current cells contents, (and they are having to do this individually for each cell...the open folder dialog box appears, and shows the main folder on the server, and the files (tediously one at a time, a different dialog box for each one) appear, and they have to reclick on the old cells that were used in the original folder to update the Grand totals sheet. There are about 12-24 sheets they have to do this with, one dialog box at a time. How do they update their formulas in the Grand totals sheet to see the same files they always used in the folders with the new names. It seems like when they walk through the steps of opening them once and reclicking on the cells they are adding together, that the Grand total sheet would remember that next time but it doesn't. They have to go through the whole process again. Does this make sense. It's really the biggest mess I have ever seen just trying to update formulas on the Grand Total sheet now having to visit each sheet seperately and adding the same old formulas together over and over again. Automatically update formulas link is checked in the Options dialog box, so that's not it. HELP!!!!
    Nannette M. Padgett.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Broken Link in workbooks (Excel 2000)

    No answers, only possibly daft questions....

    FIrstly, I assume that the Grand Total sheet IS being saved after refreshing all these changed links? (Told you the questions were daft...)

    Second.. You make reference to '12 other workbooks '... It's not these 'other' workbooks that have the external references, to the databases per chance? i.e. Do you need to go through these '12 other workbooks ' one at a time, open them to refresh their links and then save them?

    Only other suggestion I have if folks are going to be renaming files/paths, is to put their locations into cells/variables and use INDIRECT/ADDRESS or similar functions in VBA to navigate to/manage future changes.

    Hope this helps stimulate a few thoughts. I'm sure others will jump in with mor relevant advice.

    Regards
    Peter

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Broken Link in workbooks (Excel 2000)

    You could start by telling Excel NOT to update links. Then when the file has opened, choose Edit, links and change each linked file's source to the required file. Thus you change *all* links to that particular file in one go.
    Also, I know Bill Manville (find him on the Excel MVP page below) is contemplating to build a link manager. Dunno how far he's got so far.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Broken Link in workbooks (Excel 2000)

    Jan Karel & j.peter.orourke
    Thank you both for answering me. I appreciate both inputs very, very much. Jan, I believe that your method may work. I printed out your answer, and gave it to my student today. I did not go through it with him however, so will await his word on how he does as he gives it a try. It would be a logical next step.
    Again, Thanks for all you do!!!!!!
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Broken Link in workbooks (Excel 2000)

    Hi Nanette,
    I'd recommend against using INDIRECT in this case - it only works with external links when the source workbooks are open.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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