Is there a way to create a list of formulae used in a (Excel 2007)
worksheet?
I can paste range names, but would like to do the same for formulae.
Thank you for any suggestions.

One method...........CTRL + ` to switch to formula view.

Second method.................VBA to place all formulas on a new worksheet.

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

On Wed, 11 Feb 2009 10:10:52 -0800 (PST), wrote:

Is there a way to create a list of formulae used in a (Excel 2007)
worksheet?
I can paste range names, but would like to do the same for formulae.
Thank you for any suggestions.

Thank you very much, for the courtesy and promptness of your response.

On Feb 11, 1:24*pm, Gord Dibben <gorddibbATshawDOTca wrote:
One method...........CTRL + ` to switch to formula view.

Second method.................VBA to place all formulas on a new worksheet.

Hey, that's a very nice way of feeding back.

Why can't everyone be so polite ? (rhetorical question !!)

Pete

On Feb 11, 7:30*pm, wrote:
Thank you very much, for the courtesy and promptness of your response.

You are probably looking for a summary, but you can use Ctrl+~ (or whatever is to
left of the "1" number row key) to show formulas, and you can install "GetFormula"
as described in http://www.mvps.org/dmcritchie/excel/formula.htm to show the
formula used in another cell.

Would suggest also installing "GetFormula" into the right-click menu to help create
the formula as described in
http://www.mvps.org/dmcritchie/excel/rightclick.htm

Both can still be done in Excel 2007 and you can avoid referring to the ribbon
to use it if you implement both parts.

--
HTH,
David McRitchie
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

<kansaskannan wrote...
Is there a way to create a list of formulae used in a (Excel 2007)
worksheet?
I can paste range names, but would like to do the same for formulae.
Thank you for any suggestions.