Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 510
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 293
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display cell ref for MAX formula bookman3 Excel Discussion (Misc queries) 7 October 17th 08 06:31 AM
formula does not display the resulting value in the cell Liliana New Users to Excel 2 July 19th 07 08:50 AM
Formula to display cell reference (ex.. B7) Irv Excel Worksheet Functions 10 May 9th 06 10:37 AM
Display formula in cell Trish Excel Discussion (Misc queries) 1 May 13th 05 09:14 AM
Display answer only in another cell of one containing a formula Mally Excel Discussion (Misc queries) 5 January 21st 05 02:07 PM


All times are GMT +1. The time now is 06:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"