A cell contains the following calculation. =(28.36x38697/1000) and the answer
for this calculation is displayed in the cell. I want to know whether any
formula will display the numbers etc.,that has been used for the calculation
so that the one can have a the figures that have been used for the
calculation at a glance by placing the same in a cell beneath it.

Not sure about show in a cell beneath the formula, but if you please Ctrl
and the ` (key usually below Esc) this will show formula of all cells rather
than result of formula. To revert, press Ctrl and ` again.

HTH

"srinivasan" wrote in message
...
A cell contains the following calculation. =(28.36x38697/1000) and the
answer
for this calculation is displayed in the cell. I want to know whether any
formula will display the numbers etc.,that has been used for the
calculation
so that the one can have a the figures that have been used for the
calculation at a glance by placing the same in a cell beneath it.

"Rob" wrote:

Not sure about show in a cell beneath the formula, but if you please Ctrl
and the ` (key usually below Esc) this will show formula of all cells rather
than result of formula. To revert, press Ctrl and ` again.

HTH

"srinivasan" wrote in message
...
A cell contains the following calculation. =(28.36x38697/1000) and the
answer
for this calculation is displayed in the cell. I want to know whether any
formula will display the numbers etc.,that has been used for the
calculation
so that the one can have a the figures that have been used for the
calculation at a glance by placing the same in a cell beneath it.

No. This wont solve the problem. I want a formula that displays the exact contents of the cell figures to be displayed in a cell

I think you would need a little VBA to do that.
Open the VB editor (Alt F11 or similar). Menu Insert Module. Paste this
into the module:

Function DisplayFormula(Cel As Range) As String
DisplayFormula = Cel(1).Formula
End Function

Return to Excel. With your calculation in cell A1, enter this in A2:
=DisplayFormula(A1)

HTH. Best wishes Harald

"srinivasan" skrev i melding
...
A cell contains the following calculation. =(28.36x38697/1000) and the
answer
for this calculation is displayed in the cell. I want to know whether any
formula will display the numbers etc.,that has been used for the
calculation
so that the one can have a the figures that have been used for the
calculation at a glance by placing the same in a cell beneath it.

Copy the formula to the cell below, and then:

Remove the equal sign,
OR
Add an apostrophe ( ' ) in front of the equal sign.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"srinivasan" wrote in message
...


"Rob" wrote:

Not sure about show in a cell beneath the formula, but if you please Ctrl
and the ` (key usually below Esc) this will show formula of all cells
rather
than result of formula. To revert, press Ctrl and ` again.

HTH

"srinivasan" wrote in message
...
A cell contains the following calculation. =(28.36x38697/1000) and the
answer
for this calculation is displayed in the cell. I want to know whether
any
formula will display the numbers etc.,that has been used for the
calculation
so that the one can have a the figures that have been used for the
calculation at a glance by placing the same in a cell beneath it.

No. This wont solve the problem. I want a formula that displays the exact
contents of the cell figures to be displayed in a cell

"Harald Staff" wrote:

I think you would need a little VBA to do that.
Open the VB editor (Alt F11 or similar). Menu Insert Module. Paste this
into the module:

Function DisplayFormula(Cel As Range) As String
DisplayFormula = Cel(1).Formula
End Function

Return to Excel. With your calculation in cell A1, enter this in A2:
=DisplayFormula(A1)

HTH. Best wishes Harald

Thank you. I got it. it works fine.

"srinivasan" skrev i melding
...
Thank you. I got it. it works fine.

Good. (I feared you needed a recursive version :-) Thanks for the feedback.

Best wishes Harald

I've a workbook containing this function. Download my
Excel_Calendar.xls from:
http://www.pvv.org/~nsaa/excel.html#21

I've named it ShowFormula:
=ShowFormula(A1)

In the same manner I've made a function showing the note in a Cell
=NoteShow(A1)

The Functions
Public Function ShowFormula(ByRef rngCell As Range) As String
'************************************************* *****************************
'
' Macro ShowFormula created 2004-08-13 by Nikolai Sandved
)
'
' Description: This Function returns the Formula in the input Cell
rngCell as
' text
'
' Input:
' rngCell - A cell
'
' Output
' RankStatistical - Formula in the input Cell rngCell as text
'
'************************************************* *****************************

'** Error Control
On Error GoTo ErrorHandle

ShowFormula = rngCell.Formula

Exit Function

ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$ & Chr(13) _
& "In Custom function ShowFormula")
End Function


Public Function NoteShow(ByRef rngCell As Range) As String
'************************************************* *****************************
'
' Macro NoteShow created 2004-06-21 by Nikolai Sandved )
'
' Description: This Function returns the Note in the input Cell rngCell
'
' Input:
' rngCell - A cell Range
'
' Output
' ShowNote - Returns the Note as text from the Cell reference given
'
'************************************************* *****************************

'** Error Control
On Error GoTo ErrorHandle

'** Define variables
Dim wbm As Workbook 'The active workbook
Dim wsWithNote As Worksheet 'The worksheet with the Note
Dim strWorkbookName As String 'The Workbook with the Note

'** Declare variables
strWorkbookName = Mid(rngCell.Address(1, 1, 1, 1), _
InStr(rngCell.Address(1, 1, 1, 1), "[") + 1, _
InStrRev(rngCell.Address(1, 1, 1, 1), "]") - _
InStr(rngCell.Address(1, 1, 1, 1), "[") - 1)
Set wbm = Workbooks(strWorkbookName)
Set wsWithNote = wbm.Sheets(rngCell.Worksheet.Name)

NoteShow =
Application.WorksheetFunction.Clean(wsWithNote.Ran ge(rngCell.Address).NoteText)

'Cleans
Set wsWithNote = Nothing
Set wbm = Nothing

Exit Function

ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$ & Chr(13) _
& "In Custom function NoteShow")

'Cleans
Set wsWithNote = Nothing
Set wbm = Nothing

End Function

A few comments on the NoteShow function.

First, Notes are superseded by Comments.

Second, is there a reason why you just don't have rngCell.NoteText
rather than all the gyrations regarding workbook and worksheet and
range address?

Third, according the XL VBA help, NoteText returns at the most 255
characters at a time. It is the developer's responsibility to loop and
(re)create the complete note.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
I've a workbook containing this function. Download my
Excel_Calendar.xls from:
http://www.pvv.org/~nsaa/excel.html#21

I've named it ShowFormula:
=ShowFormula(A1)

In the same manner I've made a function showing the note in a Cell
=NoteShow(A1)

The Functions
Public Function ShowFormula(ByRef rngCell As Range) As String
'************************************************* *****************************
'
' Macro ShowFormula created 2004-08-13 by Nikolai Sandved
)
'
' Description: This Function returns the Formula in the input Cell
rngCell as
' text
'
' Input:
' rngCell - A cell
'
' Output
' RankStatistical - Formula in the input Cell rngCell as text
'
'************************************************* *****************************

'** Error Control
On Error GoTo ErrorHandle

ShowFormula = rngCell.Formula

Exit Function

ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$ & Chr(13) _
& "In Custom function ShowFormula")
End Function


Public Function NoteShow(ByRef rngCell As Range) As String
'************************************************* *****************************
'
' Macro NoteShow created 2004-06-21 by Nikolai Sandved )
'
' Description: This Function returns the Note in the input Cell rngCell
'
' Input:
' rngCell - A cell Range
'
' Output
' ShowNote - Returns the Note as text from the Cell reference given
'
'************************************************* *****************************

'** Error Control
On Error GoTo ErrorHandle

'** Define variables
Dim wbm As Workbook 'The active workbook
Dim wsWithNote As Worksheet 'The worksheet with the Note
Dim strWorkbookName As String 'The Workbook with the Note

'** Declare variables
strWorkbookName = Mid(rngCell.Address(1, 1, 1, 1), _
InStr(rngCell.Address(1, 1, 1, 1), "[") + 1, _
InStrRev(rngCell.Address(1, 1, 1, 1), "]") - _
InStr(rngCell.Address(1, 1, 1, 1), "[") - 1)
Set wbm = Workbooks(strWorkbookName)
Set wsWithNote = wbm.Sheets(rngCell.Worksheet.Name)

NoteShow =
Application.WorksheetFunction.Clean(wsWithNote.Ran ge(rngCell.Address).NoteText)

'Cleans
Set wsWithNote = Nothing
Set wbm = Nothing

Exit Function

ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$ & Chr(13) _
& "In Custom function NoteShow")

'Cleans
Set wsWithNote = Nothing
Set wbm = Nothing

End Function

"Tushar Mehta" wrote:

A few comments on the NoteShow function.

First, Notes are superseded by Comments.

Second, is there a reason why you just don't have rngCell.NoteText
rather than all the gyrations regarding workbook and worksheet and



Continuation of my query, I have another one. Even though the code
perfectly reproduces the formula contained in the cell it only shows the cell
reference of the formula. But I have given a name for the cell and the said
name is not displayed but the cell reference (row & col no) only has been
displayed. Is it possible to amend the formula /code to make it display the
exact contents of the formula (name given to the cell) rather than the cell
reference wherever the formula contains names. i.e instead of =L122*L119/1000
the formula should show -26.93*_36347/1000


















range address?

Third, according the XL VBA help, NoteText returns at the most 255
characters at a time. It is the developer's responsibility to loop and
(re)create the complete note.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
I've a workbook containing this function. Download my
Excel_Calendar.xls from:
http://www.pvv.org/~nsaa/excel.html#21

I've named it ShowFormula:
=ShowFormula(A1)

In the same manner I've made a function showing the note in a Cell
=NoteShow(A1)

The Functions
Public Function ShowFormula(ByRef rngCell As Range) As String
'************************************************* *****************************
'
' Macro ShowFormula created 2004-08-13 by Nikolai Sandved
)
'
' Description: This Function returns the Formula in the input Cell
rngCell as
' text
'
' Input:
' rngCell - A cell
'
' Output
' RankStatistical - Formula in the input Cell rngCell as text
'
'************************************************* *****************************

'** Error Control
On Error GoTo ErrorHandle

ShowFormula = rngCell.Formula

Exit Function

ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$ & Chr(13) _
& "In Custom function ShowFormula")
End Function


Public Function NoteShow(ByRef rngCell As Range) As String
'************************************************* *****************************
'
' Macro NoteShow created 2004-06-21 by Nikolai Sandved )
'
' Description: This Function returns the Note in the input Cell rngCell
'
' Input:
' rngCell - A cell Range
'
' Output
' ShowNote - Returns the Note as text from the Cell reference given
'
'************************************************* *****************************

'** Error Control
On Error GoTo ErrorHandle

'** Define variables
Dim wbm As Workbook 'The active workbook
Dim wsWithNote As Worksheet 'The worksheet with the Note
Dim strWorkbookName As String 'The Workbook with the Note

'** Declare variables
strWorkbookName = Mid(rngCell.Address(1, 1, 1, 1), _
InStr(rngCell.Address(1, 1, 1, 1), "[") + 1, _
InStrRev(rngCell.Address(1, 1, 1, 1), "]") - _
InStr(rngCell.Address(1, 1, 1, 1), "[") - 1)
Set wbm = Workbooks(strWorkbookName)
Set wsWithNote = wbm.Sheets(rngCell.Worksheet.Name)

NoteShow =
Application.WorksheetFunction.Clean(wsWithNote.Ran ge(rngCell.Address).NoteText)

'Cleans
Set wsWithNote = Nothing
Set wbm = Nothing

Exit Function

ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$ & Chr(13) _
& "In Custom function NoteShow")

'Cleans
Set wsWithNote = Nothing
Set wbm = Nothing

End Function

I guess you mean only shows the cell references *within* the formula,

If you had used defined names in the formula you would have seen those defined names in the formula..
If you had cell addresses in the formula you would see those cell addresses.

If you are trying go get an address changed to a defined name that is impossible,
because you could have a lot of different name that include a single cell.

Show FORMULA or FORMAT of another cell
http://www.mvps.org/dmcritchie/excel/formula.htm


"srinivasan" wrote in message news:1EBC6DD2-5BA1-49E3-969F-
Continuation of my query, I have another one. Even though the code
perfectly reproduces the formula contained in the cell it only shows the cell
reference of the formula. But I have given a name for the cell and the said
name is not displayed but the cell reference (row & col no) only has been
displayed. Is it possible to amend the formula /code to make it display the
exact contents of the formula (name given to the cell) rather than the cell
reference wherever the formula contains names. i.e instead of =L122*L119/1000
the formula should show -26.93*_36347/1000


















range address?

Third, according the XL VBA help, NoteText returns at the most 255
characters at a time. It is the developer's responsibility to loop and
(re)create the complete note.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
I've a workbook containing this function. Download my
Excel_Calendar.xls from:
http://www.pvv.org/~nsaa/excel.html#21

I've named it ShowFormula:
=ShowFormula(A1)

In the same manner I've made a function showing the note in a Cell
=NoteShow(A1)

The Functions
Public Function ShowFormula(ByRef rngCell As Range) As String
'************************************************* *****************************
'
' Macro ShowFormula created 2004-08-13 by Nikolai Sandved
)
'
' Description: This Function returns the Formula in the input Cell
rngCell as
' text
'
' Input:
' rngCell - A cell
'
' Output
' RankStatistical - Formula in the input Cell rngCell as text
'
'************************************************* *****************************

'** Error Control
On Error GoTo ErrorHandle

ShowFormula = rngCell.Formula

Exit Function

ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$ & Chr(13) _
& "In Custom function ShowFormula")
End Function


Public Function NoteShow(ByRef rngCell As Range) As String
'************************************************* *****************************
'
' Macro NoteShow created 2004-06-21 by Nikolai Sandved )
'
' Description: This Function returns the Note in the input Cell rngCell
'
' Input:
' rngCell - A cell Range
'
' Output
' ShowNote - Returns the Note as text from the Cell reference given
'
'************************************************* *****************************

'** Error Control
On Error GoTo ErrorHandle

'** Define variables
Dim wbm As Workbook 'The active workbook
Dim wsWithNote As Worksheet 'The worksheet with the Note
Dim strWorkbookName As String 'The Workbook with the Note

'** Declare variables
strWorkbookName = Mid(rngCell.Address(1, 1, 1, 1), _
InStr(rngCell.Address(1, 1, 1, 1), "[") + 1, _
InStrRev(rngCell.Address(1, 1, 1, 1), "]") - _
InStr(rngCell.Address(1, 1, 1, 1), "[") - 1)
Set wbm = Workbooks(strWorkbookName)
Set wsWithNote = wbm.Sheets(rngCell.Worksheet.Name)

NoteShow =
Application.WorksheetFunction.Clean(wsWithNote.Ran ge(rngCell.Address).NoteText)

'Cleans
Set wsWithNote = Nothing
Set wbm = Nothing

Exit Function

ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$ & Chr(13) _
& "In Custom function NoteShow")

'Cleans
Set wsWithNote = Nothing
Set wbm = Nothing

End Function