Forum Discussion

Richard Rice's avatar
Richard Rice
Copper Contributor
Jun 18, 2018

Problems with formula updating due to move to new computer

I recently moved to a new computer and find that formulas in files that reference a central worksheet no longer update because the files can't be located.  The problem stems from the fact that the na...
excel
Damien_Rosario's avatar
Damien_Rosario
Silver Contributor
Jun 19, 2018

Hi Richard

 

You could update the formulas to be more dynamic (always better to do that up front where possible), but if it's too much work, you could also try to create a new folder called Richard in the Users directory (if you have access to do so otr can get a system admin to do it for you with read/write access).

 

You can then place whatever file/s your formulas are referrencing in there to restore the connection.

 

Not a proper fix as you are still limited to the fixed location, but may get you going until you work out what you want to do in the long run?

 

Good luck!

 

Cheers

Damien

 

  • Richard Rice's avatar
    Richard Rice
    Copper Contributor
    Jun 19, 2018

    Damien,

    Thanks very much for your reply.  It looks like the easiest thing might be to just edit the source of the files that formula can't find.  If I choose Update and then Edit when I open a file with a bad link I am prompted to edit the source for different files.  I may just do this.

     

    I'm curious though what you mean by updating the formulas to be more dynamic?  A sample formula might refer to a cell in c:\Users\Richard\Excel Data\Personal\Financial\SourceFile.xlxs.  How would you make that more dynamic?

     

    Richard

    • Damien_Rosario's avatar
      Damien_Rosario
      Silver Contributor
      Jun 20, 2018

      Hi Richard

       

      Apologies, I just re-read your initial post and took cell reference as wanting a relative formula to open and access other workbooks rather than directly referencing the cell content in your open workbook.

       

      As far as I know there's nothing that can do relative cell referencing as Excel changes the path into a full path when you close the source file that you are referencing.

       

      If you happen to have a number of adjacent cells from another workbook, you could fix up the first cells connection and then remove the $ from the column/row as needed, and then autofill the formula to fill in the other cells.

       

      I have also found that if you maintain the folder structure (e.g. Financials and all sub folders where your Excel files are stored) that when you open your workbook it will prompt you to update the links which should realign the links to the correct location (at least that's been my experience).

       

      Hopefully it won't be too painful to do.

       

      Best wishes for your task ahead!

       

      Cheers

      Damien

Resources