Mass change of hyperlinks

I hope I'm in the right place. I have a workbook that contains 4000 rows. In one sheet, I have a field that is populated by a reference to another sheet (i.e.- =OtherSheet!G345) The 1st sheet is a summary sheet, and the 2nd is a detail sheet. I had set up individual hyperlinks the hard way... one at a time. Now the data has changed and I need to modify the hyperlinks. On the example of =OtherSheet!G345, the hyperlink would need to point to OtherSheet!H345. This seems simple as it could be, but I'm too stupid to figure it out. Has anyone done this? I've found a bunch of VBA to change static directories, but can't seem to modify them to fit this case. Thank you.

Its not exactly clear what you want. If every hyperlink is one column to the right and written as you show, just copy the formulas to the next column and delete the original column

Welcome to the forum!

I am not sure which kind of hyperlink you are using. Maybe if you just selected the reference cells and did a find/replace it would do it for you. Select reference cells > Ctrl+F > By Formula > Replace > oldsheet name in find > newsheet name in replace > ok.

Sorry to not be clear. I have a workbook; basically the output of a report for management. Page 1 is a summary page and Page 2 is detail. On page, column A, row 1, I have the formula ='Sheet2'!G1 to place the value of that cell (G1) in A1 on sheet 1. Also in Sheet 1, column A,
row 1, I created a hyperlink to 'Sheet2!H2 That field is the key to that record and shows whoever uses this workbook what detail goes with the summary without having to search through the detail sheet. I am trying to make it easy for anyone to view the status. I just need to
figure a way to not have to manually change all the hyperlinks without having to do all 4000 manually. Yes, as I was working with a smaller subset of records, I manually entered all the hyperlinks.
I hope that is clearer.

You can attach a file. Click go Go Advanced button in lower right of a reply. Click the Manage Hyperlinks link below the reply box.

As I said, I don't know if you are using formula or link method.

For link method:

Please Login or Register  to view this content.

I'm working on a Gov't contract and can't post specifics. Not sure what I need to tell you. After I have populated the cell on Sheet1 with a value in a cell on Sheet2 (='Sheet2'!G1), I then go to Hyperlink or Edit Hyperlink and then select Place in This Document. I enter the cell value I want to jump to (from Sheet1 to Sheet2) in the Type the cell reference: box. I then select Sheet2 in the section below if it is not already selected. That's it. Thank you for your help. I found similar VBA code and couldn't get it to work.

Solve problems as you would normally. If you solve one, you solve all of them.

Make one example, obfuscate sensitive data and then post it. That is how you solve proprietary issues.

I guess I could make one but it is easier if you help us help you.

For sheet selection and cell selections, the macro would be a bit different.

What you have to decide, is change all on one worksheet, all in all worksheets, all in sheet1 column A links, etc.

Thanx. I am up on a deadline and will attempt to just power through it. Once I get it delivered, I will dummy one up to show what I did and want to do. Thank you for all your help.

I just did it for a sheet. If needed, one for all sheets is easily done.

Please Login or Register  to view this content.

Originally Posted by mehmetcik

This worked for me:-

Please Login or Register  to view this content.

So in your Scenario try

OtherSheet!G

Please Login or Register  to view this content.

That looks closer to what I need. When I initially set up the Summary page (Sheet1), it defaulted every hyperlink to 'Sheet1!B4' In Sheet1, the text for cell E1 (for example), points to
a cell in Sheet2 (Detail page), for example; ='Sheet2'!G1, which puts the proper data in the cell on Sheet 1. I then need the hyperlink, for that cell, to take the user to cell H1 in Sheet2. I thought, since there was already a reference (='Sheet2'!G1), I could somehow use that to globally change the hyperlinks to 'Sheet2'!H1.

I am going to a meeting in a few minutes and will, more than likely, be told to abandon this effort and find a way to do it for next month; I have to run and set up this report each month. Thank you for your help. If I get the brute force method taken away, then I will create a dummy example spreadsheet to show you what I have and what I want to do.