Excel Busn Math 06: Relative & Absolute Cell References

I have a formula, like for instance = A1*A3

I like to copy this, relatively, that is updating it to =A2*A4 and so on.

The data is supposed to be in A1, A3 or A2, A4, but if the user moves the
data from A1 to B1 the fomula is updated to =B1*A3.

Is there someway I can prevent that? So no matter where the user moves the
data, the formula will remain unchanged?

Jan

I have a relatively simple Excel sheet where a user will paste in 20 fields
of information from another application. Then to the right are 10 formula
fields that need to be copied down. Record count will change each time the
template is used. Is there a way to have a macro determine how far to copy
down and then do the copy down?

I wanted to do an xlDown to bottom of pasted area, then tell it to go over
one field, mark with an "x" to spot the bottom, then do a copydown to the
"x". Problem I am having is telling Excel to go over absolute 1 field
regardless of row location. Macro keeps recording actual cell address not
relative movement.

Can anyone help?

c-

Hi all,

I have a large range of cells containing formulas, however, I just realized that I made all the references relative when I actually need them to be absolute.

In other words - my formula is
=IF(isnumber(FEB!AB1),(FEB!AB1),""))
but I need it to be
=IF(isnumber(FEB!$AB$1),(FEB!$AB$1),""))

Is there a way to change the references from relative to absolute over the entire range without going into each cell and changing them? Some sort of search/replace, perhaps, or a simple macro I could cut and paste? TIA.

Can somebody help me with editing this simple macro. I would like to perform
the following task on any cell in my spreadsheet.

=3+(Cell address one row up)[Enter key][Down Arrow]

You'd think this is pretty simple. But I can't get the macro to execute on
any cell other than the one in which I created the macro. Pretty useless.

Also, how do you record "Right Arrow", "Left Arrow", Down Arrow", and "Up
Arrow" into macros? Would seem to be pretty basic, but I sure can't figure it
out.

Thanks a lot.

C. S. Weiller

Hi,

Is there a formula (or similar) that can be used to change relative cell reference formulas to absolute cell reference formulas. I have tables that I need to move on a spreedsheet but I need them to keep thier absolute cell references.

Thanks!

I would like to change a range of cell references from relative to absolute. I would like to perform this change at one go (for eg. via Find & Replave).

What i need to do is for e.g. i want to change:

A B C
1 =C3+C5 =C2+C9 =C10+C12
2 =D3+D5 =D2+D9 =D10+D12


TO:

A B C
1 =$C$3+$C$5 =$C$2+$C$9 =$C$10+$C$12
2 =$D$3+$D$5 =$D$2+$D$9 =$D$10+$D$12


Any ideas?


regards,

Hello all,

I have found the following macro online which is supposed to allow me to select multiple cells and have the formulas within them change from relative to absolute references. Unfortunately the macro does not seem to work for me. Not being a VBA expert, can somebody please look at the following code and see if they can spot the error? Very much appreciated.

Sub MakeAbsoluteorRelativeFast()
'Written by OzGrid Business Applications
'www.ozgrid.com

Dim RdoRange As Range

Dim i As Integer

Dim Reply As String


'Ask whether Relative or Absolute


Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _

& "Relative row/Absolute column = 1" & Chr(13) _

& "Absolute row/Relative column = 2" & Chr(13) _

& "Absolute all = 3" & Chr(13) _

& "Relative all = 4", "OzGrid Business Applications")


'They cancelled

If Reply = "" Then Exit Sub

On Error Resume Next

'Set Range variable to formula cells only

Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)


'determine the change type

Select Case Reply

Case 1 'Relative row/Absolute column


For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)


Next i


Case 2 'Absolute row/Relative column


For i = 1 To RdoRange.Areas.Count


RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)


Next i



Case 3 'Absolute all


For i = 1 To RdoRange.Areas.Count


RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)


Next i



Case 4 'Relative all



For i = 1 To RdoRange.Areas.Count



RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

Next i




Case Else 'Typo



MsgBox "Change type not recognised!", vbCritical, _

"OzGrid Business Applications"



End Select


'Clear memory



Set RdoRange = Nothing



End Sub

Hello Bos ? anyone can help me, please..
How to change from absolute references to relative references.

Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365

Thank's b4

I've got 4 columns of data in one spreadsheet that are linked to a source spreadsheet. It turns out that the data that I actually need to link to is 13 columns to the right of what I thought it should have been in the source spreadsheet. I can copy and paste the cells in the main spreadsheet 13 columns to the right, and they will be linking to the correct values in the source spreadsheet, but I can't seem to figure out a way to move those formulas back 13 columns to the left without the formulas changing. Is there a way that I can make them all absolute references to the source sheet or is there another easier way to get the correct links working?

Thank you.

1. Select the range of cells containing the formulas and press Ctrl+H or
select Home -> Find & Select (in Editing Group) -> Replace to open Find & Replace dialog box.
2. In the Find what box, type the equal (=) symbol.
3. In the Replace with box, type the # symbol (to change the formulas to text).
4. Click Replace All, and then click Close.
5. Copy and paste the cells to a new location.
6. Repeat steps 1 through 3 while reversing the # and = symbols (to change the text to formulas).