Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I am trying to troubleshoot a multi-page spreadsheet that someone else messed up, and I'd like to view the formulas -- in their entirety -- to do that. Using cntr + ` (grave accent) shows formulas in cells, but if the formulas in the cells happen to be smaller than the cell width, they can't be completely seen.
Lotus and Quattro had an option of printing out the formulas separately, as in: A1: This is a test A2: +b4/e6 A3: @if($c$12<"HELLO",4,"HI") etc. Is there anyway to get that with Excel? I'm using 2003. Thanks in advance for your replies. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
John Walkenbach's macro will give you the formulas, addresses and current values
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 Fri, 14 Sep 2007 17:19:15 +0100, Orgelizer wrote: I am trying to troubleshoot a multi-page spreadsheet that someone else messed up, and I'd like to view the formulas -- in their entirety -- to do that. Using cntr + ` (grave accent) shows formulas in cells, but if the formulas in the cells happen to be smaller than the cell width, they can't be completely seen. Lotus and Quattro had an option of printing out the formulas separately, as in: A1: This is a test A2: +b4/e6 A3: @if($c$12<"HELLO",4,"HI") etc. Is there anyway to get that with Excel? I'm using 2003. Thanks in advance for your replies. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Viewing formulas | New Users to Excel | |||
Shortcut for viewing formulas | Excel Discussion (Misc queries) | |||
Viewing in web | Charts and Charting in Excel | |||
viewing formulas | Excel Discussion (Misc queries) | |||
Viewing Pictures | Excel Discussion (Misc queries) |