Restablish Links after converted to addresses

Dr Dew 2

New Member
Joined
Jan 13, 2010
Messages
2
Here's a challenge to the Excel Guru's out there.

I have a workbook that is linked to many other books. Each week the path of the link changes to a different folder. I've parsed the path into several cells and only need to change a single cell with the new date and all of the parsed cells change according to the new date.

My problem is, after remerging the path into the destination cell(s), I'm trying reactivate that cell as a Link. So far, I've copied and pasted the values in the cell to change it from a formula to an address. Then when I select the cell (Edit Mode) and just hit Enter, it will become an active link again. Problem is, I have over 10,000 cells that need to be reactivated. This is a weekly report so this process is done regularly. Any ideas or suggestions????

Thanks for your expertise.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Thanks for the views from others. I resolved the issue by pasting the text string into the cells and then I used the Sendkeys method to cycle through all of the cells with a [F2] and [Enter] through the range. It works like a charm in case anyone else has a similar requirement or need.
 
Upvote 0
You can also do the same without VBA by doing a find/replace on the equal sign, which has the same effect as F2 + Enter:
Find: =
Replace With: =

I've yet to diagnose the precise cause - you'd like to think this wouldn't be necessary.
 
Upvote 0
Thanks Xenou,
We used that method whrn we had only about 350 links, but now we have between 11,000 and 33,000 links to update and the VBA method works for us better and with fewer user interventions.
 
Upvote 0
Thanks for the feedback. This is a common problem, so if you'd like to share your solution in more detail I believe it could be of help to others. Glad you're on track now.

Cheers,
ξ
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top