#### yippie_ky_yay

##### Board Regular
Hello all,

I need to change a large amount of links.

I have all the links set except for the sheet name (ie in column D, I have "='R9988-555-222'!\$B\$5" copied and pasted for the whole column because it will always be B5 - it's just the 'R9988-555-222' that will change).

What I need to do is take the value from column A (which is the contract number - like R9988-555-222) and replace the contract number in that link.

Any help would be greatly appreciated!

-Sean

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
This is not an elegant solution, but maybe it will help.

On your column to be linked (column d), do a find and replace on the equal (=) sign, replacing it with nothing so that you have text,

In a column to the right of your previous links, try the substitute function:

=SUBSTITUTE(D2,"R9988-555-222",A2)

Then do a find & replace to get your equals sign back in and your formula back.

Hope this helps.

This should work:

Code:
``````Sub Test()
Const OldLink As String = "R9988-555-222"
Dim ARange As Range
Dim c As Range
Set ARange = Range("A1:A" & Range("A65536").End(xlUp).Row)
For Each c In ARange
On Error Resume Next
Next c
End Sub``````

This will work but it may not be very eloquent:

1. In cell D1 enter the formula
="QQ"&text(a1,"")&"!b5"

2. copy the formula down

3. highlight the formulas and press "F2" "F9"

4. do an edit find "qq" and replace with "="

I defer to the elegance of Andrew and the eloquence of barrydb.

Perhaps a more modular solution is to use the indirect function. Using cells with the contract number and the information cell reference can be combined to create text with the exact reference that is needed. Using indirect you can then get the value from the field referenced. Ie.

a1 = \$B\$5
a2 = Sheet1
a3 = "'" & a2 & "'!" & a1
a4 = indirect(a3)

This will return the info in B5 on Sheet1. To the extent that you want to change the contract ref you just need to change a2. Hope this helps.

Trouble is ... INDIRECT requires the source to be open.

Thank you all so much for answering (and so quickly too!).

Andrew's was the first I tried and it worked (some difficulty with the name though because of the dashes which excel sees as minus). I just changed the Replacement part with :="'" & c.Value & "'"
Thank you all again,
-Sean
This message was edited by yippie_ky_yay on 2002-09-23 11:54

Replies
5
Views
232
Replies
0
Views
189
Replies
1
Views
179
Replies
8
Views
274
Replies
4
Views
195

1,203,727
Messages
6,056,970
Members
444,899
Latest member
Excel_Temp

### 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.

### Which adblocker are you using?

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

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