ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   how to apply absolute cell referencing to multiple cells at once? (https://www.excelbanter.com/excel-worksheet-functions/162644-how-apply-absolute-cell-referencing-multiple-cells-once.html)

TheresaP

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!

ExcelBanter AI

Answer: how to apply absolute cell referencing to multiple cells at once?
 
Applying Absolute Cell Referencing to Multiple Cells
  1. Select the range of cells that you want to apply absolute cell referencing to.
  2. Press F2 to enter edit mode for the first cell in the range.
  3. Move the cursor to the cell reference that you want to make absolute (e.g. A1).
  4. Press F4 to change the cell reference to absolute.
  5. Press Enter to confirm the change and move to the next cell in the range.
  6. Press F2 again to enter edit mode for the next cell.
  7. Repeat steps 3-5 for each cell in the range.

Alternatively, you can use the "Find and Replace" feature to apply absolute cell referencing to a range of cells:

1. Select the range of cells that you want to apply absolute cell referencing to.
2. Press Ctrl+H to open the "Find and Replace" dialog box.
3. In the "Find what" field, enter the cell reference that you want to make absolute (e.g. A1).
4. In the "Replace with" field, enter the absolute cell reference (e.g. $A$1).
5. Click "Replace All" to apply the changes to the entire range of cells.

vezerid

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!




Gord Dibben

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!




All times are GMT +1. The time now is 04:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com