akm
Thu May 08 18:25:00 CDT 2008
Gord
Thank you for the info !
Am not very experienced with macros, but will give it a try.
Thanks again.
akm
"Gord Dibben" wrote:
> 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
>
> On Thu, 8 May 2008 07:17:01 -0700, akm <akm@discussions.microsoft.com> wrote:
>
> >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.
> >
> >
> >
> >"Roger Govier" wrote:
> >
> >> 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
> >> --
> >> Regards
> >> Roger Govier
> >>
> >> "akm" <akm@discussions.microsoft.com> wrote in message
> >> news:A69B9DC1-90DB-46FE-90B7-86277A9FB0A5@microsoft.com...
> >> > 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/communities/newsgroups/en-us/default.aspx?query=absolute+reference&dg=microsoft.public.excel&cat=en_US_e064c9ec-14bf-4ef7-ba07-6a1970559f64&lang=en&cr=US&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 ?
> >> > --
> >> > akm
> >> > Thanks again for your help.
> >> > ----
> >> > Ah, the miracle of computers!... someone also said "To error is human, but
> >> > to really screw up it takes a computer!"
> >>
>
>