Please help save me tons of work!!!

[yippie_ky_yay]

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

 

[briankelly]

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)

Copy this down your column,
Then do a find & replace to get your equals sign back in and your formula back.

Hope this helps.

 

[Andrew Poulsom]

This should work:

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
        c.Offset(0, 3).Replace What:=OldLink, Replacement:=c.Value
    Next c
End Sub

 

[barrydb]

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 "="

 

[briankelly]

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

 

[skay4242]

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.

 

[Andrew Poulsom]

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

 

[yippie_ky_yay]

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