keeping formulas static when cutitng and pasting associated celldata
 

Hello,

I have a column with a repeated formula working out something. The formula
accesses data in the same row in other columns - e.g this goes down one
column (this is from row 619):
=IF(D619<"",D619-SUM(G619:AB619)-$N$2,
IF(C619<"",E617+C619-SUM(G619:AB619), E617 - SUM(G619:AB619)))

When I cut and paste cells in the column referenced by the formula cell,
Excel changes the formula accordingly messing everything up.

In frustration I want to change all references to have '$' before them e.g.
$D$619 but to do this by hand would take many many hours due to the hundreds
of rows. Is there any easy way to do this or stop Excel changing my formulas
on a cut and paste? BTW. I can copy, paste and then delete to avoid the
problem but it is inconvenient so not my preference.

Thanks,
Ben

keeping formulas static when cutitng and pasting associated cell data
 

A VBA way, put this code in a normal module and run the macro's as
appropriate. It will change the absolute referencing of the range you select
as I think want.
If you are unfamiliar with using VBA code, post back and I (or someone) will
guide you through it.
BTW, this code isnt mine, I got it from one of these groups a long time ago,
sorry but I cant remember who from! Thanks though whoever you were,
Regards,
Alan.

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

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

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

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

"ben" wrote in message
...
Hello,

I have a column with a repeated formula working out something. The formula
accesses data in the same row in other columns - e.g this goes down one
column (this is from row 619):
=IF(D619<"",D619-SUM(G619:AB619)-$N$2,
IF(C619<"",E617+C619-SUM(G619:AB619), E617 - SUM(G619:AB619)))

When I cut and paste cells in the column referenced by the formula cell,
Excel changes the formula accordingly messing everything up.

In frustration I want to change all references to have '$' before them
e.g.
$D$619 but to do this by hand would take many many hours due to the
hundreds
of rows. Is there any easy way to do this or stop Excel changing my
formulas
on a cut and paste? BTW. I can copy, paste and then delete to avoid the
problem but it is inconvenient so not my preference.

Thanks,
Ben

keeping formulas static when cutitng and pasting associated celldata
 

Thanks. I'll give it a go.

Right now cutting and pasting usually results in a cell turning into #REF!
which is very bothersome. I have no idea why some columns causes the #REF!
problem and other columns are fine.

regards,
Ben

Alan wrote:
A VBA way, put this code in a normal module and run the macro's as
appropriate. It will change the absolute referencing of the range you select
as I think want.
If you are unfamiliar with using VBA code, post back and I (or someone) will
guide you through it.
BTW, this code isnt mine, I got it from one of these groups a long time ago,
sorry but I cant remember who from! Thanks though whoever you were,
Regards,
Alan.

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

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

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

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

"ben" wrote in message
...

Hello,

I have a column with a repeated formula working out something. The formula
accesses data in the same row in other columns - e.g this goes down one
column (this is from row 619):
=IF(D619<"",D619-SUM(G619:AB619)-$N$2,
IF(C619<"",E617+C619-SUM(G619:AB619), E617 - SUM(G619:AB619)))

When I cut and paste cells in the column referenced by the formula cell,
Excel changes the formula accordingly messing everything up.

In frustration I want to change all references to have '$' before them
e.g.
$D$619 but to do this by hand would take many many hours due to the
hundreds
of rows. Is there any easy way to do this or stop Excel changing my
formulas
on a cut and paste? BTW. I can copy, paste and then delete to avoid the
problem but it is inconvenient so not my preference.

Thanks,
Ben