display formulas as text

W

Will Fleenor

How to display (for documentation and debugging) in one column the literal
textual representation of Excel formulas that are in another column?

OS: Windows XP all SPs and patches loaded.
AP: Excel 2003 all SPs and patches loaded.

Thanks, Will
 
L

Lori

To convert formulas to text you can use:
data>text to columns>next>next>text (assuming tab delimited)

Copy this to another column and then convert back to values with:
data>text to columns>finish (assuming tab delimited)
 
W

Will Fleenor

Two good suggestions, thanks, but not what I was looking for. I would like
to have formulas in column B that are calculated in their normal manner.
Then in column C I would like to have a formula that would display the
forumla that is ine the corresponding cell in column B.

Example:
Contents of cell B1: =45*10
Display in B1: 450
Contents of cell C1:
Display in cell C1: =45*10

I need a formula in cell C1 because the contents of cell B1 can change and I
want the displayed formula in cell C1 to also change.

Thanks, Will
 
B

Beege

Will said:
How to display (for documentation and debugging) in one column the literal
textual representation of Excel formulas that are in another column?

OS: Windows XP all SPs and patches loaded.
AP: Excel 2003 all SPs and patches loaded.

Thanks, Will
Click to expand...
Will,

control-tilde (Ctrl+~) will toggle between formula and results views...

Beege
 
G

Gord Dibben

Will

Function ShowFormula(cell)
ShowFormula = "No Formula"
If cell.HasFormula Then ShowFormula = cell.Formula
End Function

Copy/paste into a general module in your workbook.

=ShowFormula(cellref)


Gord Dibben MS Excel MVP
 
O

Oldersox

Thanks Lori,

This is exactly what I was looking for (on a different post).

Small suggestion. To simplify end result change the column data format (in
the Convert Text to Columns Wizard - Step 3 of 3) to 'Text' and change the
destination on the same popup to the cell(s) where you want the result to
appear. This makes the formula in the cells in question immediately appear
as text in the cell location you specify.

Again I thankyou for this solution, you have really saved me a lot of work
(and stress)

Kind regards
Oldersox (Geoff)
 
Top