Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Applying absolute cell referencing to many cell in Excel

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 24-02-2012
Member
 
Join Date: Jan 2011
Posts: 12
Applying absolute cell referencing to many cell in Excel

Hi, I know that you press F4 to change the cell referencing to absolute, relative or mixed. But is there a way to select a range of cells to apply the change all at once instead of having to choose each one and pressing F4 for each. Any help is greatly appreciated. Thanks.

Reply With Quote
  #2  
Old 24-02-2012
Member
 
Join Date: Jul 2011
Posts: 618
Re: Applying absolute cell referencing to many cell in Excel

I tried a simple formula: =A1+A2+A3+A4. I selected all cell refs and pressed F4. All of the refs turned to absolute. This is as far as changing the references within *a single formula*. If you change the refs in a single formula and then copy this cell, the copied cell refs will inherit the refs of the original formula.
Reply With Quote
  #3  
Old 24-02-2012
Member
 
Join Date: Jul 2011
Posts: 631
Re: Applying absolute cell referencing to many cell in Excel

Here are a few macros for you to choose from.Select your range of cells and run the macro.
Code:
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)
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)
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)
Next
End Sub
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Applying absolute cell referencing to many cell in Excel"
Thread Thread Starter Forum Replies Last Post
How to disable excel for taking empty cell in Excel chart Hache hi MS Office Support 2 25-02-2012 12:30 PM
Cell format errors appears in Excel 2003 but not in Excel 2000 @nki$achi MS Office Support 1 24-01-2012 05:00 PM
How to restore cell gridlines lost after coloring the cell in Excel? Chitti's Windows Software 2 04-01-2012 06:54 PM
Auto-populate a MS Word table cell with text from a diff cell? dreamrthts Windows Software 5 23-03-2009 10:39 PM
Cell referencing in VBA Antonio00 MS Office Support 2 08-02-2008 11:05 PM


All times are GMT +5.5. The time now is 12:23 PM.