global vs relative format

[Guest]

i am linking cells in a workbook to another workbook. the link gives me the
$c$45 format which does not allow me to copy to other cells that are
contiguous.

how and where do you change the format so it gives you c45 as the reference
when linking?

thanks.

 

[Guest]

F4 will cycle you through the various types of references.

 

[Gord Dibben]

I don't believe you can change the default to relative cell references.

To change one cell at a time select the address in the formula bar then use F4
to cycle through to options.

To change all copied cells at once from absolute to relative use this macro.

Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula _
(cell.Formula, xlA1, xlA1, xlRelative)
Next
End Sub


Gord Dibben MS Excel MVP

i am linking cells in a workbook to another workbook. the link gives me the
$c$45 format which does not allow me to copy to other cells that are
contiguous.

how and where do you change the format so it gives you c45 as the reference
when linking?

thanks.

Gord Dibben MS Excel MVP