Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Location
    Albuquerque, New Mexico, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Literal format for formulas? (2k)

    Is there a way to format a cell so that a formula is displayed literally, rather than its computed value?

    Tools / Options / View and checking "Formulas" does this globally, but I'd like to be able to do the same display format with individual cell(s), not all of them.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Literal format for formulas? (2k)

    Not as far as I know. You could prefix an individual formula with an apostrophe ' but of course that means that the value of the cell will be the text of the formula.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Literal format for formulas? (2k)

    As Hans says, it isn't possible for a single cell to display its formula and return its value. However, in an adjacent cell you could use this user-defined function to display the formula:

    Public Function ShowFormula(rngIn As Range) As String
    If rngIn.HasFormula Then
    ShowFormula = rngIn.Formula
    Else
    ShowFormula = rngIn.Value
    End If
    End Function

    If you add this to your Personal.xls, you'll need to use the following syntax:

    =Personal.xls!ShowFormula(<cell address or cell range name>)
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    New Lounger
    Join Date
    Dec 2001
    Location
    Albuquerque, New Mexico, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Literal format for formulas? (2k)

    John,
    Thank you! The ShowFormula UDF is exactly what I needed...
    George

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Literal format for formulas? (2k)

    You can format the individual cell as "TEXT" and it will display its formula. This is similar to adding an apostrophe. It does lose its "value" and it acts like a string

    Steve

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Literal format for formulas? (2k)

    Hi George,

    Another way to do this is to copy the formula into the cell's comment box. This avoids any complications that might arise if there is something useful in the adjacent cell, though any comments you might already have would get clobbered instead. Another thing to note is that, by default, worksheet comments don
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •