How to 'format' cells to be 'absolute' references ?

[akm]

How to 'format' cells to be 'absolute' references ?
Trying to setup cells with date formulas (for project management) with
formula cell to show date from sum of reference date cell plus number of days
cell, and make formula cell an absolute value so can sort cells by that cell
column (per discussion at ...
http://www.microsoft.com/communitie...&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us
....ie, cell-3 = $cell-1(date) + $cell-2(number of days)
How to get reference cells to be absolute values by formating, or set with
shortcut key, or set with main menu pick ?

 

[Roger Govier]

Hi
In cell A3 for example
=$A$1+$A$2
Format cell A3 in whatever way you wish to have the date shown
Format>Cells>Number>Custom>dd mmm yyyy

 

[akm]

Roger
Thank you for the reply.
Guess Im using the word 'format' figuratively in that I would like to be
able to make all cells have absolute values in formulas etc without needing
to go to each cell and click F4 at each entry of the formula/reference to get
the $ in front of each ref.
Hope that makes more sense as a question.
akm
Thanks again for your help.

Hi
In cell A3 for example
=$A$1+$A$2
Format cell A3 in whatever way you wish to have the date shown
Format>Cells>Number>Custom>dd mmm yyyy

 

[Gord Dibben]

You cannot format or pre-set cells with absolute references.

You can run a macro on your formulas after you have entered them is about all
you get.

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

I have these as option buttons on a UserForm.

Accessed through my cells' right-click menu.


Gord Dibben MS Excel MVP

Roger
Thank you for the reply.
Guess Im using the word 'format' figuratively in that I would like to be
able to make all cells have absolute values in formulas etc without needing
to go to each cell and click F4 at each entry of the formula/reference to get
the $ in front of each ref.
Hope that makes more sense as a question.
akm
Thanks again for your help.

Hi
In cell A3 for example
=$A$1+$A$2
Format cell A3 in whatever way you wish to have the date shown
Format>Cells>Number>Custom>dd mmm yyyy

 

[akm]

Gord
Thank you for the info !
Am not very experienced with macros, but will give it a try.
Thanks again.
akm