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

To convert formulas to text you can use:
datatext to columnsnextnexttext (assuming tab delimited)

Copy this to another column and then convert back to values with:
datatext to columnsfinish (assuming tab delimited)

Will Fleenor wrote:

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

Tools Options ViewTab check the FORMULAS box

Vaya con Dios,
Chuck, CABGx3



"Will Fleenor" wrote:

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

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


"CLR" wrote in message
...
Tools Options ViewTab check the FORMULAS box

Vaya con Dios,
Chuck, CABGx3



"Will Fleenor" wrote:

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

Will Fleenor wrote:
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


Will,

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

Beege

I have no "formula" solution as you requested, but here is a crude macro that
seems to do the job..........

Private Sub Worksheet_SelectionChange(ByVal target As Excel.Range)
'For target cell in column B
'If formula, place text version of formula in adjacent cell in column C
'If empty or contains text or number, leave adjacent cell alone
Dim f As String
If target.Cells.Column = 2 Then
If Not target.Formula = True Then
If target.Formula = target.Value Then
End
Else
target.Select
f = Selection.Formula
Selection.Offset(0, 1).Value = "'" & f
End If
Else
End If
End If
End Sub

hth
Vaya con Dios,
Chuck, CABGx3




"Will Fleenor" wrote:

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


"CLR" wrote in message
...
Tools Options ViewTab check the FORMULAS box

Vaya con Dios,
Chuck, CABGx3



"Will Fleenor" wrote:

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

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


On Tue, 9 Jan 2007 07:30:09 -0700, "Will Fleenor" wrote:

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


"CLR" wrote in message
...
Tools Options ViewTab check the FORMULAS box

Vaya con Dios,
Chuck, CABGx3



"Will Fleenor" wrote:

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

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)

"Lori" wrote:

To convert formulas to text you can use:
datatext to columnsnextnexttext (assuming tab delimited)

Copy this to another column and then convert back to values with:
datatext to columnsfinish (assuming tab delimited)

Will Fleenor wrote:

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