Results 1 to 8 of 8
Thread: To see formulas.

20010424, 17:28 #1
 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?

20010424, 17:42 #2
 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

20010424, 17:51 #3
 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.

20010424, 17:54 #4
 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~.

20010424, 18:12 #5
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 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:
CtrlShift1 (! exclamation mark) will format selected cells in the #,##0.00 number format
CtrlShift2 (@ at symbol) will format selected cells in the #0.00 AM time format
CtrlShift3 (# pound symbol in the US) will format selected cells in the ddmmmyy date format
CtrlShift4 ($ dollar sign) will format selected cells in the $#,##0.00 currency format
CtrlShift5 (% percent sign) will format selected cells in the #0% percentage format
CtrlShift6 (^ caret sign) will format selected cells in the 0.00E+00 scientific notation format
CtrlShift7 (& ampersand or and sign) will put a border around the selected cells
and my favorite
Alt= does the same as the Autosum iconJohn ... I float in liquid gardens
UTC 7ąDS

20010424, 18:28 #6
 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

20010424, 19:12 #7
 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.

20010424, 19:35 #8
 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.