Howto: relative & absolute cell references

Greetings,

I hope my first posting has found the correct group to ask my question. I
made the mistake of referencing the main worksheet to many other worksheets
and when copying or moving the required cells, the cell references change.
Is it possible to change many cell references (or an entire worksheet) at
one time from relative to absolute? Or will I need to change each reference
manually?

Thanks,

Eric

Hi Eric,

You are okay in this group, or you could have chosen
microsoft.public.excel.misc. People here will try to help where possible.

You have to be careful in converting relative to absolute. Sometimes you
want the formula to change as you move things about.

In any event, to respond to your question, I will provide you with a small
VBA routine from Gord Dibben. I saw his solution earlier and thought it was
very good, so I saved it. Select the cells you want changed, and then use
his macro.

If you haven't used a macro before, then follow these steps:

1) Alt F11 to go to the Visual Basic Editor (VBE)
2) Insert | Module
3) Cut and paste Gord's routine below ...start at first Sub and end at last
Sub...into the module
4) Select the cells you want changed
5) Tools | Macro | Macros or F8 and engage "Absolute"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Macro to change relative to absolute.

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

Gord Dibben XL2002
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hope that helps.

Regards,
Kevin



"Eric Tubbs" wrote in message
...
Greetings,

I hope my first posting has found the correct group to ask my question. I
made the mistake of referencing the main worksheet to many other
worksheets
and when copying or moving the required cells, the cell references change.
Is it possible to change many cell references (or an entire worksheet) at
one time from relative to absolute? Or will I need to change each
reference
manually?

Thanks,

Eric

Kevin,

Thanks! Your help is dearly welcomed. What I am working on is a "poor mans"
way to use excel for documenting Acess tables and graphing capabilities. I
am not working with 'formulas' per say, but cell references from other
worksheets in the same workbook and a few other workbooks. I am a beginner
to Excel & Access and having loads of fun.

I made sure that a reference to Gordon has been made for the code snippet
and to you for helping me out. Since this is a project I am working on, I
have to document any help I receive along the way.

Thanks for your help,

Eric

"Kevin Stecyk" wrote in message
...
Hi Eric,

You are okay in this group, or you could have chosen
microsoft.public.excel.misc. People here will try to help where possible.

You have to be careful in converting relative to absolute. Sometimes you
want the formula to change as you move things about.

In any event, to respond to your question, I will provide you with a small
VBA routine from Gord Dibben. I saw his solution earlier and thought it
was
very good, so I saved it. Select the cells you want changed, and then use
his macro.

If you haven't used a macro before, then follow these steps:

1) Alt F11 to go to the Visual Basic Editor (VBE)
2) Insert | Module
3) Cut and paste Gord's routine below ...start at first Sub and end at
last
Sub...into the module
4) Select the cells you want changed
5) Tools | Macro | Macros or F8 and engage "Absolute"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Macro to change relative to absolute.

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

Gord Dibben XL2002
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hope that helps.

Regards,
Kevin



"Eric Tubbs" wrote in message
...
Greetings,

I hope my first posting has found the correct group to ask my question.
I
made the mistake of referencing the main worksheet to many other
worksheets
and when copying or moving the required cells, the cell references
change.
Is it possible to change many cell references (or an entire worksheet)
at
one time from relative to absolute? Or will I need to change each
reference
manually?

Thanks,

Eric

Eric,

You are most welcome--glad the solution worked for you.

Regards,
Kevin



"Eric Tubbs" wrote in message
...
Kevin,

Thanks! Your help is dearly welcomed. What I am working on is a "poor
mans"
way to use excel for documenting Acess tables and graphing capabilities. I
am not working with 'formulas' per say, but cell references from other
worksheets in the same workbook and a few other workbooks. I am a beginner
to Excel & Access and having loads of fun.

I made sure that a reference to Gordon has been made for the code snippet
and to you for helping me out. Since this is a project I am working on, I
have to document any help I receive along the way.

Thanks for your help,

Eric

"Kevin Stecyk" wrote in message
...
Hi Eric,

You are okay in this group, or you could have chosen
microsoft.public.excel.misc. People here will try to help where
possible.

You have to be careful in converting relative to absolute. Sometimes
you
want the formula to change as you move things about.

In any event, to respond to your question, I will provide you with a
small
VBA routine from Gord Dibben. I saw his solution earlier and thought it
was
very good, so I saved it. Select the cells you want changed, and then
use
his macro.

If you haven't used a macro before, then follow these steps:

1) Alt F11 to go to the Visual Basic Editor (VBE)
2) Insert | Module
3) Cut and paste Gord's routine below ...start at first Sub and end at
last
Sub...into the module
4) Select the cells you want changed
5) Tools | Macro | Macros or F8 and engage "Absolute"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Macro to change relative to absolute.

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

Gord Dibben XL2002
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hope that helps.

Regards,
Kevin



"Eric Tubbs" wrote in message
...
Greetings,

I hope my first posting has found the correct group to ask my
question.
I
made the mistake of referencing the main worksheet to many other
worksheets
and when copying or moving the required cells, the cell references
change.
Is it possible to change many cell references (or an entire worksheet)
at
one time from relative to absolute? Or will I need to change each
reference
manually?

Thanks,

Eric