Showing Formulas

[trvlnmny]

Is there some way to show the formulas used and their current values
in a report?
Thanks

 

[JP]

Ctrl-` (grave) will toggle between displaying formulas and their
values. It's the character next to the number 1 on most US keyboards.

HTH,
JP

 

[trvlnmny]

Ctrl-` (grave) will toggle between displaying formulas and their
values. It's the character next to the number 1 on most US keyboards.

HTH,
JP



- Show quoted text -

I do not want to toggle between showing and hiding the formulas. I
want the formulas to be on the printed report so readers know how a
column was calculated.
Thanks

 

[Ron de Bruin]

For formulas and values see
http://www.mvps.org/dmcritchie/excel/formula.htm

 

[JP]

Gotcha, go to Tools>Options>View tab, check box next to "Formulas"


HTH,
JP

 

[Gord Dibben]

From John Walkenbach's storehouse of macros.

Sub ListFormulas()
'from John Walkenbach
Dim FormulaCells As Range, cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer
Dim ws As Worksheet
' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No Formulas."
Exit Sub
End If

' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

' Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

' Process each formula
Row = 2
For Each cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
With FormulaSheet
Cells(Row, 1) = cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & cell.Formula
Cells(Row, 3) = cell.Value
Row = Row + 1
End With
Next cell

' Adjust column widths
FormulaSheet.Columns("A:C").Cells.WrapText = True ''AutoFit
Application.StatusBar = False
End Sub


Gord Dibben MS Excel MVP