If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to remove the cell reference in a sheet at once
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??? |
#2
|
|||
|
|||
How to remove the cell reference in a sheet at once
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" wrote in message ... 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??? |
#3
|
|||
|
|||
How to remove the cell reference in a sheet at once
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??? . |
#4
|
|||
|
|||
How to remove the cell reference in a sheet at once
You can use the editreplace 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 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??? |
Thread Tools | |
Display Modes | |
|
|