T
trvlnmny
Is there some way to show the formulas used and their current values
in a report?
Thanks
in a report?
Thanks
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
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
values. It's the character next to the number 1 on most US keyboards.
HTH,
JP
T
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
R
Ron de Bruin
For formulas and values see
http://www.mvps.org/dmcritchie/excel/formula.htm
http://www.mvps.org/dmcritchie/excel/formula.htm
J
JP
Gotcha, go to Tools>Options>View tab, check box next to "Formulas"
HTH,
JP
HTH,
JP
G
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
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