Forum Discussion
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 name of the Users folder in the new computer (which is c:\Users\Richa) is different from the Users folder name (Users\Richard) on the old computer.
This is really inconvenient. Do I need to hunt down and update every cell reference to different worksheets?
Richard
p.s. I would have used the same Users folder name as the old computer but Windows did this for me, using the default first 5 letters of my email.
3 Replies
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
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
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
