I have a personal check register spreadsheet that I created in January, 2009 and have used every day since. In nearly 15 years of use, it has grown to 7 tabs and over 13,000 line items. In spite of its size, it is basically a simple spreadsheet containing data like checks, deposits, Venmo or Zelle payments, cash transfers, etc. Until recently I never encountered any major problems, and I was always able to fix the minor issues on my own. The latest issue, however, has me stumped and I've been unable to figure a solution.
Each line item has a cell which contains a hyperlink that points to a unique image file, i.e., .jpg or .pdf. So if I click on the displayed text (I use ASCII 254 or "■") in the hyperlink, the image is displayed. It might be a credit card statement or a cancelled check or anything else I want to be able to view.
In the address line for creating the hyperlink, Excel allows you to browse to a specific file location. When you finish, the address Excel has created does not include the drive letter but instead a reference to it that I do not understand. If, for example, a .jpg copy of check #1234 exists in folder "Checks" on drive C:, Excel initially created an address of "..\..\..\..\checks\1234.jpg" I've copied and pasted this type of address thousands of times over the years changing the specific data in the address and have never had a problem. If I had initially overwritten the format used by Excel to refer to which physical drive it was accessing, i.e., "c:\checks\1234.jpg" and had copied THAT address thousands of times, I would have been able to view the image file and would not have the problem I am currently encountering.
Over 15 years, I've moved this spreadsheet back and forth dozens of times between computers I have in two locations. The addresses using the "..\..\..\.." to refer to drive C: have never caused any issues.
I recently purchased a new computer which came installed with Windows 11. This is the first time I've used Windows 11 and I mention it only in the spirit of full disclosure. I have no idea if it is in any way a part of my problem.
On the new computer I installed the version of Excel I've been using for a while now, i.e. Home and Student 2016. After an uneventful and successful install, I copied all of my Excel files to the same folder as before. The file "Checks.xlsx" loads as it should but here (FINALLY !!) is the problem. Clicking on any of the hyperlinks returns the message "The address of this site is not valid. Check the address and try again." I've discovered two changes to the hyperlink which make the address "valid". One change is to replace the "..\..\..\.." with "..". The other change that works is to replace the "..\..\..\.." with "c:". That's fine one line at a time but as each line item in the spreadsheet contains 2 hyperlinks, I would need to manually change nearly 25,000 of them to make this spreadsheet wholly functional again.
I've tried the Search and Replace function but cannot figure a way to do this. Is there an efficient fix to this problem or do I need to manually fix, let's say, 20 a day for 3½ years?
Bookmarks