Prev: Count number of days from the last entry?
Next: i deleted info in excel then saved. can i recover deleted info?
From: The Greek on 25 May 2010 01:12
Hi,

I have a sheet where there are many cells are fix (reference cells)
using the F4 key. Is there any way i can remove the effect of the F4
without going to each formula and them keep pressing F4 to remove the
reference???
From: L. Howard Kittle on 25 May 2010 01:33
With formulas like =$A$1+$B$1 and pulled down for 30 cells, I used Edit >
Find > $ > replace with "nothing" > OK

HTH
Regards,
Howard

"The Greek" <izzalzurba(a)gmail.com> wrote in message
news:c97e7e60-2fc9-44e6-8411-977ad6c7f3fb(a)v29g2000prb.googlegroups.com...
> Hi,
>
> I have a sheet where there are many cells are fix (reference cells)
> using the F4 key. Is there any way i can remove the effect of the F4
> without going to each formula and them keep pressing F4 to remove the
> reference???


From: Pritesh on 25 May 2010 04:55
Stp1- Select range where you want to replace fixed references with relative
references.

Stp2- Press Ctrl + H, this will show Replace dialogue box.

Stp3- In "Find" space, type single $

Stp4- Leave "Replace" space empty

Stp5- Click "replace all".

You are done.. chk it.

Regards,
Pritesh


"The Greek" wrote:

> Hi,
>
> I have a sheet where there are many cells are fix (reference cells)
> using the F4 key. Is there any way i can remove the effect of the F4
> without going to each formula and them keep pressing F4 to remove the
> reference???
> .
>
From: Gord Dibben on 25 May 2010 09:42
You can use the edit>replace as suggested.

Or use a macro to make changes in selected cells.

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


Gord Dibben MS Excel MVP

On Mon, 24 May 2010 22:12:03 -0700 (PDT), The Greek <izzalzurba(a)gmail.com>
wrote:

>Hi,
>
>I have a sheet where there are many cells are fix (reference cells)
>using the F4 key. Is there any way i can remove the effect of the F4
>without going to each formula and them keep pressing F4 to remove the
>reference???

 | 
Pages: 1
Prev: Count number of days from the last entry?
Next: i deleted info in excel then saved. can i recover deleted info?