revealing the contents of a formula

[srinivasan]

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]

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]

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



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]

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

 

[RagDyeR]

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!
==============================================

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



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

 

[srinivasan]

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

 

[Harald Staff]

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

Best wishes Harald

 

[sandved]

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
([email protected])
'
' 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 ([email protected])
'
' 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.Range(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]

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

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
([email protected])
'
' 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 ([email protected])
'
' 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.Range(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

 

[srinivasan]

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

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
([email protected])
'
' 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 ([email protected])
'
' 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.Range(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

 

[David McRitchie]

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

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

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
([email protected])
'
' 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 ([email protected])
'
' 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.Range(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