How do I make a hyperlink follow a moved file?

[BobsYourUncle54]

Office 365:

I have a very large extensive spreadsheet I created for keeping track of customers for my business. I enter essential details, and contract amounts, along with deposits and payments until the contract is complete. There are a lot of formulas to keep track of monies paid in different categories, up to the bottom line receivables. And conditional formatting to change column colors based on input when I mark the project complete or canceled, and to deduct the dollar figures from the running totals of all projects etc etc.

I got tired of trying to do cell notes, too much info to write, and too clumsy. So instead, I created hyperlinks to open a Word document called Project Notes for each client. Here I record all information I need to keep track of. I also have other links that take me to job photo folders, costing spreadsheets and more.

I have an extensive series of folders, subfolders and so on, with each client having its own folder with all project information in it, including a Project Notes Word file for each one. The parent folders have categories, Current, Complete, Canceled etc.

Great! It all works perfect except one detail:

When the job is marked complete, I hide the column so it is no longer in the active project view. Then, in my file folders I move the individual project folder from the active current location to an archival folder, Complete, Canceled etc. There are way to many to keep them all in the same directory.

I use Windows Explorer and drag the folder to the appropriate location. So far so good.

But! This process breaks my hyperlinks. The links are static, and look for the various info in its original location that I pointed it to. Once moved, no more links.

I could go in there and edit all the links after I move the folder, but gee that is a lot of work.

Is there a way to create dynamic hyperlinks, so that when I move a folder the links update to the new location?

I thought of creating a bunch of IFS formulas, but it would have to be done by hand and every referenced file is in a different location.

Am I asking or expecting too much of Excel?

Thanks in advance

[#2]

Hi @BobsYourUncle54 

I guess there is no way to create such a dynamic link that recognizes a moved file or folder. This reason is easy: The Excel formula does not have a clue what you are doing on your file structure on the hard drive.

Even if you would use a nested HYPERLINK function (e.g. with IFS or ISERROR) it would give in error once the file is moved.

The only way that might work is a VBA solution, but this would be very specific based on your environment and requirments.

[#3]

@Martin_Weiss Thanks for your thoughts. I rather suspected this was the case, as I can logically follow the process through.

I was thinking how nice it is that if you move a cell within a sheet, the formulas will update any references to the moved cell.

I was hoping there was a way to extend this process beyond the sheet at hand.

I can't spend a large amount of time on it, it's already a lot of work maintaining the sheet as new projects come in and old ones complete. All I can think of is nesting some IFS statements to point at different directories based on the content of a cell used to indicate the project status. Again, a lot of work.

Thanks.

[#4]

To be honest, it looks to me like you are using the wrong tool here? I love Excel passionately and we can get it to do many things, but that doesn't make it the right tool. I think of the expression, "if the only tool you have is a hammer, every problem becomes a nail". You have a lot of updating going on here. There are many online platforms that are geared for this - I signed up for Monday.com lately which looks very appropriate but there are others. That may not be the answer you are looking for but I think it would be a better solution for you.

[#5]

@OliverBuckleyThanks for the thoughts on that. I had actually considered Monday.com after my daughter suggested it to me. As the controller for a large construction company, that's what she uses.

For me it may be like using a sledgehammer to swat a fly. It is a bit pricey, having to pay every month to rent it. Using a spreadsheet is free, aside from my annual 365 subscription. We are not a huge company, but I am very detail oriented and like to be able to pull info at a moments notice rather than having to dig through a bunch of files to get it.

I appreciate everyones input, thanks.