I have several similar worksheets with cross-referenced cells.
When I set the original up, I just used normal cell references, eg.
Data!AF109, Data!AF110, etc.

It would be a great convenience for me if I could re-define all these
references as absolute, so that I can copy them into a different part of the
worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.

Is there a way I can do this without re-typing all the formulae?

You can use F4 to toggle the references, or you could use a macro

I use 4 different macros,
press Alt + F11, click insertmodule and paste in

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)
Next
End Sub


Sub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelative)
Next
End Sub


Sub RelColAbsRows()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsRowRelColumn)
Next
End Sub


Sub RelRowsAbsCol()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelRowAbsColumn)
Next
End Sub


beware of line wrapping


press Alt + Q to close the VBE, now select the formulas and run the first of
them by pressing Alt + F8 and select the macro. If you want to make it
available for future workbooks you can put the macro in your
Personal.xls





"Basher Bates" wrote in message
...
I have several similar worksheets with cross-referenced cells.
When I set the original up, I just used normal cell references, eg.
Data!AF109, Data!AF110, etc.

It would be a great convenience for me if I could re-define all these
references as absolute, so that I can copy them into a different part of
the
worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.

Is there a way I can do this without re-typing all the formulae?

Thanks Peo, all new territory for me. I'll give it a go and let you know how
I get on.
I did try pressing F4 but that just cleared all the entries within the
selection and I had to "Undo".

Kind regards

Ken

"Peo Sjoblom" wrote:

You can use F4 to toggle the references, or you could use a macro

I use 4 different macros,
press Alt + F11, click insertmodule and paste in

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)
Next
End Sub


Sub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelative)
Next
End Sub


Sub RelColAbsRows()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsRowRelColumn)
Next
End Sub


Sub RelRowsAbsCol()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelRowAbsColumn)
Next
End Sub


beware of line wrapping


press Alt + Q to close the VBE, now select the formulas and run the first of
them by pressing Alt + F8 and select the macro. If you want to make it
available for future workbooks you can put the macro in your
Personal.xls





"Basher Bates" wrote in message
...
I have several similar worksheets with cross-referenced cells.
When I set the original up, I just used normal cell references, eg.
Data!AF109, Data!AF110, etc.

It would be a great convenience for me if I could re-define all these
references as absolute, so that I can copy them into a different part of
the
worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.

Is there a way I can do this without re-typing all the formulae?

Sorry, I you need to high light the formula in the formula bar, then press
F4. Note for the macros that

Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)

has to be all in one line or else you'll get a syntax error, that goes for
all 4 macros





--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Basher Bates" wrote in message
...
Thanks Peo, all new territory for me. I'll give it a go and let you know
how
I get on.
I did try pressing F4 but that just cleared all the entries within the
selection and I had to "Undo".

Kind regards

Ken

"Peo Sjoblom" wrote:

You can use F4 to toggle the references, or you could use a macro

I use 4 different macros,
press Alt + F11, click insertmodule and paste in

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)
Next
End Sub


Sub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelative)
Next
End Sub


Sub RelColAbsRows()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsRowRelColumn)
Next
End Sub


Sub RelRowsAbsCol()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelRowAbsColumn)
Next
End Sub


beware of line wrapping


press Alt + Q to close the VBE, now select the formulas and run the first
of
them by pressing Alt + F8 and select the macro. If you want to make it
available for future workbooks you can put the macro in your
Personal.xls





"Basher Bates" wrote in message
...
I have several similar worksheets with cross-referenced cells.
When I set the original up, I just used normal cell references, eg.
Data!AF109, Data!AF110, etc.

It would be a great convenience for me if I could re-define all these
references as absolute, so that I can copy them into a different part
of
the
worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.

Is there a way I can do this without re-typing all the formulae?

Thanks for your original post - and the supplementary one. I did, in fact
put the various statements all on one line each and it worked fine. this is
going to save me a lot of typing - and risk of errors!. My first attempt at
a macro!

I was not so fortunate with the last part of your first message, re saving -
Excel Help not much use either. How do I save the macros for future use?
Presumably, I need to create a file called "Personal.xls" then store the
macros there - but, then, how do I call them from another workbook? The Help
facility told me that I could make a new button for this purpose and have it
load in each new workbook - but no further info. on how to go about doing
this.

Regards,

Ken

"Peo Sjoblom" wrote:

Sorry, I you need to high light the formula in the formula bar, then press
F4. Note for the macros that

Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)

has to be all in one line or else you'll get a syntax error, that goes for
all 4 macros





--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Basher Bates" wrote in message
...
Thanks Peo, all new territory for me. I'll give it a go and let you know
how
I get on.
I did try pressing F4 but that just cleared all the entries within the
selection and I had to "Undo".

Kind regards

Ken

"Peo Sjoblom" wrote:

You can use F4 to toggle the references, or you could use a macro

I use 4 different macros,
press Alt + F11, click insertmodule and paste in

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)
Next
End Sub


Sub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelative)
Next
End Sub


Sub RelColAbsRows()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsRowRelColumn)
Next
End Sub


Sub RelRowsAbsCol()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlRelRowAbsColumn)
Next
End Sub


beware of line wrapping


press Alt + Q to close the VBE, now select the formulas and run the first
of
them by pressing Alt + F8 and select the macro. If you want to make it
available for future workbooks you can put the macro in your
Personal.xls





"Basher Bates" wrote in message
...
I have several similar worksheets with cross-referenced cells.
When I set the original up, I just used normal cell references, eg.
Data!AF109, Data!AF110, etc.

It would be a great convenience for me if I could re-define all these
references as absolute, so that I can copy them into a different part
of
the
worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.

Is there a way I can do this without re-typing all the formulae?

OK, do as follows: Do toolsmacrorecord new macro, when prompted select
Personal Macro Workbook from the dropdown in the store macro in: box, click
OK. Stop the macro recording. immediately. Press Alt + F11 to open the VBE,
in the left hand side in the project pane double click module1 in the
personal.xls. Remove any code from your recorded macro and paste in the 4
macros there. Press Alt + Q to close the VBE.
When you close excel you will be prompted to save the personal.xls, do so.
Now you will have this available for all workbooks, to run them select the
cells you want to change the references in, do Alt + F8 and select any of
the 4 macros either by high lighting one of them and click run or by double
clicking the name. Or you can create a custom menu button(s) that you can
attach any macro to

HTH


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Basher Bates" wrote in message
...
Thanks for your original post - and the supplementary one. I did, in fact
put the various statements all on one line each and it worked fine. this
is
going to save me a lot of typing - and risk of errors!. My first attempt
at
a macro!

I was not so fortunate with the last part of your first message, re
saving -
Excel Help not much use either. How do I save the macros for future use?
Presumably, I need to create a file called "Personal.xls" then store the
macros there - but, then, how do I call them from another workbook? The
Help
facility told me that I could make a new button for this purpose and have
it
load in each new workbook - but no further info. on how to go about doing
this.

Regards,

Ken

"Peo Sjoblom" wrote:

Sorry, I you need to high light the formula in the formula bar, then
press
F4. Note for the macros that

Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)

has to be all in one line or else you'll get a syntax error, that goes
for
all 4 macros





--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Basher Bates" wrote in message
...
Thanks Peo, all new territory for me. I'll give it a go and let you
know
how
I get on.
I did try pressing F4 but that just cleared all the entries within the
selection and I had to "Undo".

Kind regards

Ken

"Peo Sjoblom" wrote:

You can use F4 to toggle the references, or you could use a macro

I use 4 different macros,
press Alt + F11, click insertmodule and paste in

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlAbsolute)
Next
End Sub


Sub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlRelative)
Next
End Sub


Sub RelColAbsRows()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlAbsRowRelColumn)
Next
End Sub


Sub RelRowsAbsCol()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlRelRowAbsColumn)
Next
End Sub


beware of line wrapping


press Alt + Q to close the VBE, now select the formulas and run the
first
of
them by pressing Alt + F8 and select the macro. If you want to make it
available for future workbooks you can put the macro in your
Personal.xls





"Basher Bates" wrote in
message
...
I have several similar worksheets with cross-referenced cells.
When I set the original up, I just used normal cell references, eg.
Data!AF109, Data!AF110, etc.

It would be a great convenience for me if I could re-define all
these
references as absolute, so that I can copy them into a different
part
of
the
worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.

Is there a way I can do this without re-typing all the formulae?

Peo,
Thank you for all your trouble. Brilliant!!!!
I have left a "Yes" rating for the way and detail you have answered my query.
Thanks again.

Ken

"Peo Sjoblom" wrote:

OK, do as follows: Do toolsmacrorecord new macro, when prompted select
Personal Macro Workbook from the dropdown in the store macro in: box, click
OK. Stop the macro recording. immediately. Press Alt + F11 to open the VBE,
in the left hand side in the project pane double click module1 in the
personal.xls. Remove any code from your recorded macro and paste in the 4
macros there. Press Alt + Q to close the VBE.
When you close excel you will be prompted to save the personal.xls, do so.
Now you will have this available for all workbooks, to run them select the
cells you want to change the references in, do Alt + F8 and select any of
the 4 macros either by high lighting one of them and click run or by double
clicking the name. Or you can create a custom menu button(s) that you can
attach any macro to

HTH


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Basher Bates" wrote in message
...
Thanks for your original post - and the supplementary one. I did, in fact
put the various statements all on one line each and it worked fine. this
is
going to save me a lot of typing - and risk of errors!. My first attempt
at
a macro!

I was not so fortunate with the last part of your first message, re
saving -
Excel Help not much use either. How do I save the macros for future use?
Presumably, I need to create a file called "Personal.xls" then store the
macros there - but, then, how do I call them from another workbook? The
Help
facility told me that I could make a new button for this purpose and have
it
load in each new workbook - but no further info. on how to go about doing
this.

Regards,

Ken

"Peo Sjoblom" wrote:

Sorry, I you need to high light the formula in the formula bar, then
press
F4. Note for the macros that

Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)

has to be all in one line or else you'll get a syntax error, that goes
for
all 4 macros





--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Basher Bates" wrote in message
...
Thanks Peo, all new territory for me. I'll give it a go and let you
know
how
I get on.
I did try pressing F4 but that just cleared all the entries within the
selection and I had to "Undo".

Kind regards

Ken

"Peo Sjoblom" wrote:

You can use F4 to toggle the references, or you could use a macro

I use 4 different macros,
press Alt + F11, click insertmodule and paste in

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlAbsolute)
Next
End Sub


Sub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlRelative)
Next
End Sub


Sub RelColAbsRows()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlAbsRowRelColumn)
Next
End Sub


Sub RelRowsAbsCol()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlRelRowAbsColumn)
Next
End Sub


beware of line wrapping


press Alt + Q to close the VBE, now select the formulas and run the
first
of
them by pressing Alt + F8 and select the macro. If you want to make it
available for future workbooks you can put the macro in your
Personal.xls





"Basher Bates" wrote in
message
...
I have several similar worksheets with cross-referenced cells.
When I set the original up, I just used normal cell references, eg.
Data!AF109, Data!AF110, etc.

It would be a great convenience for me if I could re-define all
these
references as absolute, so that I can copy them into a different
part
of
the
worksheet. They would then become Data!$AF$109, Data!$AF$110, etc.

Is there a way I can do this without re-typing all the formulae?

Thanks for the feedback

Peo

"Basher Bates" wrote in message
...
Peo,
Thank you for all your trouble. Brilliant!!!!
I have left a "Yes" rating for the way and detail you have answered my
query.
Thanks again.

Ken

"Peo Sjoblom" wrote:

OK, do as follows: Do toolsmacrorecord new macro, when prompted select
Personal Macro Workbook from the dropdown in the store macro in: box,
click
OK. Stop the macro recording. immediately. Press Alt + F11 to open the
VBE,
in the left hand side in the project pane double click module1 in the
personal.xls. Remove any code from your recorded macro and paste in the 4
macros there. Press Alt + Q to close the VBE.
When you close excel you will be prompted to save the personal.xls, do
so.
Now you will have this available for all workbooks, to run them select
the
cells you want to change the references in, do Alt + F8 and select any of
the 4 macros either by high lighting one of them and click run or by
double
clicking the name. Or you can create a custom menu button(s) that you can
attach any macro to

HTH


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Basher Bates" wrote in message
...
Thanks for your original post - and the supplementary one. I did, in
fact
put the various statements all on one line each and it worked fine.
this
is
going to save me a lot of typing - and risk of errors!. My first
attempt
at
a macro!

I was not so fortunate with the last part of your first message, re
saving -
Excel Help not much use either. How do I save the macros for future
use?
Presumably, I need to create a file called "Personal.xls" then store
the
macros there - but, then, how do I call them from another workbook?
The
Help
facility told me that I could make a new button for this purpose and
have
it
load in each new workbook - but no further info. on how to go about
doing
this.

Regards,

Ken

"Peo Sjoblom" wrote:

Sorry, I you need to high light the formula in the formula bar, then
press
F4. Note for the macros that

Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1,
xlAbsolute)

has to be all in one line or else you'll get a syntax error, that goes
for
all 4 macros





--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Basher Bates" wrote in
message
...
Thanks Peo, all new territory for me. I'll give it a go and let you
know
how
I get on.
I did try pressing F4 but that just cleared all the entries within
the
selection and I had to "Undo".

Kind regards

Ken

"Peo Sjoblom" wrote:

You can use F4 to toggle the references, or you could use a macro

I use 4 different macros,
press Alt + F11, click insertmodule and paste in

Sub ReltoAbs()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlAbsolute)
Next
End Sub


Sub AbstoRel()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlRelative)
Next
End Sub


Sub RelColAbsRows()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlAbsRowRelColumn)
Next
End Sub


Sub RelRowsAbsCol()
Dim Cell As Range
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1,
xlA1,
xlRelRowAbsColumn)
Next
End Sub


beware of line wrapping


press Alt + Q to close the VBE, now select the formulas and run the
first
of
them by pressing Alt + F8 and select the macro. If you want to make
it
available for future workbooks you can put the macro in your
Personal.xls





"Basher Bates" wrote in
message
...
I have several similar worksheets with cross-referenced cells.
When I set the original up, I just used normal cell references,
eg.
Data!AF109, Data!AF110, etc.

It would be a great convenience for me if I could re-define all
these
references as absolute, so that I can copy them into a different
part
of
the
worksheet. They would then become Data!$AF$109, Data!$AF$110,
etc.

Is there a way I can do this without re-typing all the formulae?