A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

how to apply absolute cell referencing to multiple cells at once?



 
 
Thread Tools Display Modes
  #1  
Old October 18th 07, 05:12 PM posted to microsoft.public.excel.worksheet.functions
TheresaP
external usenet poster
 
Posts: 1
Default how to apply absolute cell referencing to multiple cells at once?

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!
Ads
  #2  
Old October 18th 07, 05:23 PM posted to microsoft.public.excel.worksheet.functions
vezerid
external usenet poster
 
Posts: 751
Default how to apply absolute cell referencing to multiple cells at once?

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.

Does this help?
Kostis Vezerides

On Oct 18, 7:12 pm, TheresaP >
wrote:
> 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!



  #3  
Old October 19th 07, 01:08 AM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,911
Default how to apply absolute cell referencing to multiple cells at once?

Here are a few macros for you to choose from.

Select your range of cells and run the macro.

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


Gord Dibben MS Excel MVP

On Thu, 18 Oct 2007 09:12:03 -0700, TheresaP
> wrote:

>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!


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Apply Absolute Reference to multiplie cells lostinformulas Excel Worksheet Functions 6 June 19th 06 06:45 PM
apply arithmetic functions on special multiple cells Zhekka Excel Worksheet Functions 5 February 10th 06 03:04 AM
apply a formula to multiple cells FixitFrog Excel Discussion (Misc queries) 3 June 23rd 05 11:54 AM
Making multiple cells absolute at once Jamie A Miller Excel Discussion (Misc queries) 2 May 25th 05 01:13 AM
How do I apply a combo box to multiple cells in Excel so that it . DB Excel Discussion (Misc queries) 1 January 12th 05 04:42 PM


All times are GMT +1. The time now is 03:11 PM.


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