Is there a smarter way prompt ($) several links in an area

[PATSYS]

I have an area, say A1:B100 which is linked to an area of another workbook, C1:D100. I want to be able to copy the link to several tables of the same size and as such I want the link to be constant i.e. prompted ($). For example in A1, the link would be "=[File.xls]Sheet1!$C$1" instead of just =[File.xls]Sheet1!C1. This way I can copy the formula to another location with it adjusting in column/rows.

I can go to each cells and press F4 but my problem is of course I have quite a number of cells to do it.

Is there an easier way?

Thanks

 

[Tom Urtis]

If your question is, how do you convert a range of formulas so all their cell references are made to be absolute, then select your range of interest and run this macro:



Sub Test1()
With Application
.ScreenUpdating = 0
Dim x As Range, y$, z$
On Error Resume Next
For Each x In Selection.SpecialCells(3)
y = x.Formula
z = .ConvertFormula(Formula:=y, fromReferenceStyle:=xlA1, toReferenceStyle:=xlA1, toAbsolute:=xlAbsolute)
x.Formula = z
Next
Err.Clear
.ScreenUpdating = 1
End With
End Sub



If you mean something else, please repost.