Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 25th 10, 06:12 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 14
Default 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   Report Post  
Old May 25th 10, 06:33 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 698
Default 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   Report Post  
Old May 25th 10, 09:55 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 26
Default 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   Report Post  
Old May 25th 10, 12:22 PM
Senior Member
 
First recorded activity by ExcelBanter: Sep 2008
Location: Hyderabad
Posts: 237
Thumbs up

Quote:
Originally Posted by The Greek View Post
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???

select the content and use Cltr + H (find and replace for $).

note: please make sure it effect only selected cells not the entire spreadsheet.

all the best
__________________
Thanks
Bala
  #5   Report Post  
Old May 25th 10, 02:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,909
Default 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???




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting a sheet name reference within a cell reference??? Broyston Excel Discussion (Misc queries) 9 July 8th 08 08:35 PM
multiple cell reference from sheet to sheet KMR R.A. Excel Worksheet Functions 0 May 29th 08 12:48 AM
Changing sheet reference to cell reference TeeJay Excel Worksheet Functions 3 October 19th 07 11:50 AM
How do I remove a reference from a cell? stayathome working girl Excel Discussion (Misc queries) 1 April 18th 06 08:52 PM
How can I remove cell reference when calculating outside of the pi Doit Excel Worksheet Functions 0 October 3rd 05 05:31 PM


All times are GMT +1. The time now is 07:53 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017