Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    May 2008
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    To see formulas.

    I have a spreadsheet with formulas, oriented in columns.
    I would like to have a last column, with the text seen formula.
    When I type CTRL ~ , the formulas come, but even copying the column into text formatted cells, it doesn't work.
    Any suggestion what I ignore or I'm doing wrong?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: To see formulas.

    What exactly are you trying for. CTRL~ applies a date format, do you want to view dates as text like 1st January 2001 instead of 1/1/2001 ?. Or do you actually want to view the formula instead of the result ?

    Andrew C

  3. #3
    Lounger
    Join Date
    May 2008
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: To see formulas.

    Thanks for your answer.
    I want to have both, two columns, one with the results and the other with the formulas which generated them, at the same time.
    This is useful because will enable the formulas (and therefore the procedure I'm using), used to be audited.
    Thanks again for your help.

  4. #4
    Lounger
    Join Date
    May 2008
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: To see formulas.

    Sorry for inducing confusion.
    What I actually use to see the formulas in text is CTRL` , rather than CTRL~.

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

    Re: To see formulas.

    In XL97 Ctrl ` (without the shift key) toggles formula display, and is essentially the same as Tools, Options, View, Formulas.

    If this is a one time need, with the formulas I'd replace all = signs with !=, copy them to the "formula display" area, replace != with '= for all the display formulas, then replace/reinstate != with = in the source formulas. The display formulas will not update if the formulas are changed.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Speaking of keyboard shortcuts, I actually use some of these when I remember to:
    Ctrl-Shift-1 (! exclamation mark) will format selected cells in the #,##0.00 number format
    Ctrl-Shift-2 (@ at symbol) will format selected cells in the #0.00 AM time format
    Ctrl-Shift-3 (# pound symbol in the US) will format selected cells in the dd-mmm-yy date format
    Ctrl-Shift-4 ($ dollar sign) will format selected cells in the $#,##0.00 currency format
    Ctrl-Shift-5 (% percent sign) will format selected cells in the #0% percentage format
    Ctrl-Shift-6 (^ caret sign) will format selected cells in the 0.00E+00 scientific notation format
    Ctrl-Shift-7 (& ampersand or and sign) will put a border around the selected cells
    and my favorite
    Alt-= does the same as the Autosum icon
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: To see formulas.

    Thats OK. As you cannot view both the results and the actual formula itself on the one sheet at the same time, you could use a function like thr following to show the formulas in a given cell.

    Function ShowFormula(fCell As Range) As String
    ShowFormula = "["&fCell.Address & "] " & fCell.Formula
    End Function

    so that if A1 contained the formula =TODAY() then ShowFormula(A1) would return [$A$1] =TODAY()

    Hope that can be of some help, and if needed it can be enhanced or maybe somebody knows of an easier way.

    Andrew

  7. #7
    Lounger
    Join Date
    May 2008
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: To see formulas.

    Thanks a lot to all of you.
    I'll try your suggestions and let you know.
    Thanks again.

  8. #8
    Lounger
    Join Date
    May 2008
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: To see formulas.

    Thanks to your assistance, I was able to solve the situation.
    This is what I did:
    In both columns (the original and the one I got by copy), I displayed results.
    In the last one, made a CTRL C, and then on same cells, Pasted Special > Values, which got the data fozen.
    In the formulas column, I applied the suggestion of != for = ; what got the formulas frozen.

Posting Permissions

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