MrExcel Publishing
Your One Stop for Excel Tips & Solutions

showing some formulas !?


Posted by Roger on May 31, 2001 11:54 AM

Is it possible to show only the formula for one column in an Excel
spreadsheet. The Tools/Options/View checkbox for formulas or toggling
gives the formulas for all columns. I know that if you hide the other
columns then it will just show the one you want but I don't want to hide
the other columns. I would be very grateful if you can help on this.
Roger


Posted by mseyf on May 31, 2001 12:39 PM

creating a User Defined Function may be the easiest:

Function GetFormula(cell)
GetFormula = cell.Formula
End Function

if your formulas are in column A, insert a column at column B, in cell B1 enter =getformula(a1) and copy down.

Not exactly what you're looking for, but may do.

Mark

Posted by Eric on May 31, 2001 1:12 PM

could also paste the formula and add a ' to the front of it

adding a ' to the front of the formula will let you turn it into a label you could put at the head of the column if that's any use.

Posted by Raymond on May 31, 2001 4:48 PM


Here's a macro I use that for the selected cell(s)toggles between displaying the formulas and the results :-

Sub ToggleFormula()
Dim rng As Range, cell As Range
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
On Error Resume Next
For Each cell In rng
If cell.HasFormula = True Then
cell.Value = Chr(39) & cell.Formula
Else
cell = cell.Value
End If
Next cell
End Sub

If you prefer to have two buttons instead of one, then :-

Sub ShowFormulas() : _
Selection.Replace What:="=", _
Replacement:="""=", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False _
End Sub

Sub HideFormulas : _
Selection.Replace What:="""=", _
Replacement:="=", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False _
End Sub

Posted by Roger on June 01, 2001 11:41 AM

Hi Raymnond

thanks a lot for the valuable tip

Roger

Posted by Roger on June 01, 2001 11:42 AM

Re: could also paste the formula and add a ' to the front of it

hi Eric
Thanks a lot for the tip

Roger

Posted by Roger on June 01, 2001 11:44 AM

hi Mark

thanks a lot for the UDF and for the quick service!!

Roger