Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display formula on the cell
Hi
I wanted to display the formulas permanently besides a calculated value. When I go to ToolsOptionsview, click on the window options Formulas, I achieve the result however the value with the formulas in it also turn to formulas which is not the result i wanted. Could someone pls advice. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display formula on the cell
On fly:
Create an UDF Public Function ShowFormula(MyRange As Range) ShowFormula = MyRange.Formula End Function P.e. into cell A1 enter the some formula Into cell B1 enter the formula =ShowFormula(A1) Arvi Laanemets "journey" wrote in message ... Hi I wanted to display the formulas permanently besides a calculated value. When I go to ToolsOptionsview, click on the window options Formulas, I achieve the result however the value with the formulas in it also turn to formulas which is not the result i wanted. Could someone pls advice. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display formula on the cell
Hi journey,
You could add the formula to the cell comments and display those. Here's a macro to add the formulae to the cell comments for the selected range: Sub AddFormulasToComments() Application.ScreenUpdating = False Dim CommentRange As Range, TargetCell As Range, Cmt As Comment 'If the whole worksheet is selected, limit action to the used range. If Selection.Address = Cells.Address Then Set CommentRange = Range(ActiveSheet.UsedRange.Address) Else Set CommentRange = Range(Selection.Address) End If 'Delete comments from cells containing formulae. For Each Cmt In ActiveSheet.Comments For Each TargetCell In CommentRange If TargetCell.Address = Cmt.Parent.Address Then Cmt.Delete Exit For End If Next Next 'If the cell contains a formula, turn the formula into a comment. For Each TargetCell In CommentRange With TargetCell If Left(.Formula, 1) = "=" Then 'add a new comment .AddComment 'copy the formula into the comment box .Comment.Text Text:=.Formula 'display the comment .Comment.Visible = True 'autosize to fit .Comment.Shape.TextFrame.AutoSize = True 'position the comment adjacent to its cell If .Column < ActiveSheet.Columns.Count - 1 Then .Comment.Shape.IncrementLeft -11.25 If .Row 1 Then .Comment.Shape.IncrementTop 8.25 End If End With Next Application.ScreenUpdating = True MsgBox " To print the comments, choose" & vbCrLf & _ " File|Page Setup|Sheet|Comments," & vbCrLf & _ "then choose the required print option.", vbOKOnly End Sub -- Cheers macropod [Microsoft MVP - Word] "journey" wrote in message ... Hi I wanted to display the formulas permanently besides a calculated value. When I go to ToolsOptionsview, click on the window options Formulas, I achieve the result however the value with the formulas in it also turn to formulas which is not the result i wanted. Could someone pls advice. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display cell ref for MAX formula | Excel Discussion (Misc queries) | |||
formula does not display the resulting value in the cell | New Users to Excel | |||
Formula to display cell reference (ex.. B7) | Excel Worksheet Functions | |||
Display formula in cell | Excel Discussion (Misc queries) | |||
Display answer only in another cell of one containing a formula | Excel Discussion (Misc queries) |