Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying Cell Formulas
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? -- Richard |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying Cell Formulas
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 -- Brevity is the soul of wit. "Stranded" wrote: 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? -- Richard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying Cell Formulas
Stranded wrote: 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? -- Richard 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Displaying Cell Formulas
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 InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben MS Excel MVP On Fri, 8 Sep 2006 10:49:02 -0700, Stranded wrote: 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? Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying Cell Formulas | Excel Discussion (Misc queries) | |||
Excel sees cell formulas as constants, will not solve | Excel Worksheet Functions | |||
Copying formulas from cell to cell to cell to....... | Excel Discussion (Misc queries) | |||
Cell references in formulas become text | Excel Discussion (Misc queries) | |||
Displaying in a cell the formula from another cell | Excel Discussion (Misc queries) |