Displaying Cell Formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to be able to view a column of data alongside the formulas used
to calculate the data. I'm aware that all of the formulas can be displayed
via Tools/Options/Formulas and that Ctrl~ will toggle between the formulas
and values. The best I've come up with so far is to copy the formulas and
convert them to text cell by cell. At worst, is there a function to convert
the formulas to text as a group?
 
You would have to create a UDF (user-defined function), with some code like
this:

Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
If Cell.HasArray Then GetFormula = "{" & GetFormula & "}"
End Function

To create this function right click on a workbook tab, select view code, and
create a module in your workbook, then paste the above code in the module,
save, go back to your workbook and enter =getformula(A1) to return the
formula, if any, in A1.

Dave
 
Stranded said:
I would like to be able to view a column of data alongside the formulas used
to calculate the data. I'm aware that all of the formulas can be displayed
via Tools/Options/Formulas and that Ctrl~ will toggle between the formulas
and values. The best I've come up with so far is to copy the formulas and
convert them to text cell by cell. At worst, is there a function to convert
the formulas to text as a group?
Click to expand...

Yes. Delete the = in front of the formula. To do that globally, go
to the Edit Menu and select Replace. Put = in what to replace and
replace it with "".

You'll have to add a column with these "un"formulas because they are no
longer fundtioning formulas.

ed
 
Are you able to use a Macro?

This one adds a new worksheet with a list of formulas, the address and the
result.

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

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


I would like to be able to view a column of data alongside the formulas used
to calculate the data. I'm aware that all of the formulas can be displayed
via Tools/Options/Formulas and that Ctrl~ will toggle between the formulas
and values. The best I've come up with so far is to copy the formulas and
convert them to text cell by cell. At worst, is there a function to convert
the formulas to text as a group?
Click to expand...

Gord Dibben MS Excel MVP
 
Back
Top