Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a cell
to display the values for the component references making up the formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S

Not in that way, AFAIK
But look at ToolsFormula AuditingEvaluate Formula

BTW your requirement is one reason why I always recommend to do small steps
per cell, so you can easily check all intermediate result.

In your case,

Cell1: =B2+C4
Cell2: =Cell1/D2
Cell3: =A1*Cell2

--
Kind regards,

Niek Otten

"David Smith" wrote in message
...
Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a
cell to display the values for the component references making up the
formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S

Hi David

=A1&" * ("&B2&" + "&C4&") / "&D2

Regards

Roger Govier


David Smith wrote:
Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a cell
to display the values for the component references making up the formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S

Hi Roger,

It there any utility for that. I am just interest in seeing the reference
values temporarily in order to audit values against what is known that they
should be.

Thank you....

David S


"Roger Govier" wrote in message
...
Hi David

=A1&" * ("&B2&" + "&C4&") / "&D2

Regards

Roger Govier


David Smith wrote:
Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a
cell to display the values for the component references making up the
formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S

select your cell with the formula

go to the formula bar.
Highlight each cell reference in the formula successively and Do F9 on each
one. after you see the value, do escape to restore the formula. Then do
the next reference. You can select any complete subset of the formula (or
the whole formula) to evaluate it.

--
Regards,
Tom Ogilvy

"David Smith" wrote in message
...
Hi Roger,

It there any utility for that. I am just interest in seeing the reference
values temporarily in order to audit values against what is known that
they
should be.

Thank you....

David S


"Roger Govier" wrote in message
...
Hi David

=A1&" * ("&B2&" + "&C4&") / "&D2

Regards

Roger Govier


David Smith wrote:
Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a
cell to display the values for the component references making up the
formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S

Hi Tom,

Thanks, that works great. How did you find out about this or is it just a
quirk of excel, can't seem to find it in the help files? Now would it be
possible to write a function that would be able to display the whole formula
showing the numeric values of each component reference all at once?

Thanks for your help....

David S



"Tom Ogilvy" wrote in message
...
select your cell with the formula

go to the formula bar.
Highlight each cell reference in the formula successively and Do F9 on
each
one. after you see the value, do escape to restore the formula. Then do
the next reference. You can select any complete subset of the formula (or
the whole formula) to evaluate it.

--
Regards,
Tom Ogilvy

"David Smith" wrote in message
...
Hi Roger,

It there any utility for that. I am just interest in seeing the reference
values temporarily in order to audit values against what is known that
they
should be.

Thank you....

David S


"Roger Govier" wrote in message
...
Hi David

=A1&" * ("&B2&" + "&C4&") / "&D2

Regards

Roger Govier


David Smith wrote:
Hello,

Is there a way (addin utilities or otherwise) to cause the formula in
a
cell to display the values for the component references making up the
formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S

Formulas can't access other formulas, so there would be no automatic way to
do that with built in formulas. You would need to write a user defined
function in VBA, but to write a generalized function would require a
function that understands all formulas in Excel - a general formula parser
so to speak.

I don't know where I learned about F9 in the formula bar; perhaps back in
xl5 or earlier when they wrote actual manuals or perhaps read it in a hits
article or in a book.

--
Regards,
Tom Ogilvy


"David Smith" wrote in message
...
Hi Tom,

Thanks, that works great. How did you find out about this or is it just a
quirk of excel, can't seem to find it in the help files? Now would it be
possible to write a function that would be able to display the whole
formula
showing the numeric values of each component reference all at once?

Thanks for your help....

David S



"Tom Ogilvy" wrote in message
...
select your cell with the formula

go to the formula bar.
Highlight each cell reference in the formula successively and Do F9 on
each
one. after you see the value, do escape to restore the formula. Then
do
the next reference. You can select any complete subset of the formula
(or
the whole formula) to evaluate it.

--
Regards,
Tom Ogilvy

"David Smith" wrote in message
...
Hi Roger,

It there any utility for that. I am just interest in seeing the
reference
values temporarily in order to audit values against what is known that
they
should be.

Thank you....

David S


"Roger Govier" wrote in message
...
Hi David

=A1&" * ("&B2&" + "&C4&") / "&D2

Regards

Roger Govier


David Smith wrote:
Hello,

Is there a way (addin utilities or otherwise) to cause the formula
in
a
cell to display the values for the component references making up
the
formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S

On Sun, 20 Nov 2005 12:17:28 -0500, "David Smith" wrote:

Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a cell
to display the values for the component references making up the formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S



I don't know if this will be helpful, but you could also set up a Watch Window
and Watch the contents of the references.
--ron

"Ron Rosenfeld" wrote in message
...
On Sun, 20 Nov 2005 12:17:28 -0500, "David Smith" wrote:

Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a
cell
to display the values for the component references making up the formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S



I don't know if this will be helpful, but you could also set up a Watch
Window
and Watch the contents of the references.
--ron

Hi Ron,
I am not to famiiar with the visual basic editor. I can view the watch
window but how do I do what you suggest?

Thanks....

David S

On Mon, 21 Nov 2005 08:48:23 -0500, "David Smith" wrote:

"Ron Rosenfeld" wrote in message
.. .
On Sun, 20 Nov 2005 12:17:28 -0500, "David Smith" wrote:

Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a
cell
to display the values for the component references making up the formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S



I don't know if this will be helpful, but you could also set up a Watch
Window
and Watch the contents of the references.
--ron

Hi Ron,
I am not to famiiar with the visual basic editor. I can view the watch
window but how do I do what you suggest?

Thanks....

David S


This is an Excel feature, no VBA required.

View/Toolbars and select the Watch Window toolbar. You can then set a Watch
for various cells.

This is different from the Watch window in VBA.


--ron

"Ron Rosenfeld" wrote in message
...
On Mon, 21 Nov 2005 08:48:23 -0500, "David Smith" wrote:

"Ron Rosenfeld" wrote in message
. ..
On Sun, 20 Nov 2005 12:17:28 -0500, "David Smith"
wrote:

Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a
cell
to display the values for the component references making up the
formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S



I don't know if this will be helpful, but you could also set up a Watch
Window
and Watch the contents of the references.
--ron

Hi Ron,
I am not to famiiar with the visual basic editor. I can view the watch
window but how do I do what you suggest?

Thanks....

David S


This is an Excel feature, no VBA required.

View/Toolbars and select the Watch Window toolbar. You can then set a
Watch
for various cells.

This is different from the Watch window in VBA.


--ron

Hi Ron,

Tried that but it does not give me anything more than the value in the total
cell and its formula. I am interested in seeing the individual values of the
component parts of a formula as described in my original post. So I can do a
quick audit of the formula based on an assumption sheet or known values.

Thank you.....

David S

Try ToolsFormula AuditingEvaluate Formula, as I suggested in my previous
posting

--
Kind regards,

Niek Otten

"David Smith" wrote in message
...

"Ron Rosenfeld" wrote in message
...
On Mon, 21 Nov 2005 08:48:23 -0500, "David Smith"
wrote:

"Ron Rosenfeld" wrote in message
...
On Sun, 20 Nov 2005 12:17:28 -0500, "David Smith"
wrote:

Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a
cell
to display the values for the component references making up the
formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S



I don't know if this will be helpful, but you could also set up a Watch
Window
and Watch the contents of the references.
--ron

Hi Ron,
I am not to famiiar with the visual basic editor. I can view the watch
window but how do I do what you suggest?

Thanks....

David S


This is an Excel feature, no VBA required.

View/Toolbars and select the Watch Window toolbar. You can then set a
Watch
for various cells.

This is different from the Watch window in VBA.


--ron

Hi Ron,

Tried that but it does not give me anything more than the value in the
total cell and its formula. I am interested in seeing the individual
values of the component parts of a formula as described in my original
post. So I can do a quick audit of the formula based on an assumption
sheet or known values.

Thank you.....

David S

On Mon, 21 Nov 2005 09:41:12 -0500, "David Smith" wrote:


"Ron Rosenfeld" wrote in message
.. .
On Mon, 21 Nov 2005 08:48:23 -0500, "David Smith" wrote:

"Ron Rosenfeld" wrote in message
...
On Sun, 20 Nov 2005 12:17:28 -0500, "David Smith"
wrote:

Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a
cell
to display the values for the component references making up the
formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S



I don't know if this will be helpful, but you could also set up a Watch
Window
and Watch the contents of the references.
--ron

Hi Ron,
I am not to famiiar with the visual basic editor. I can view the watch
window but how do I do what you suggest?

Thanks....

David S


This is an Excel feature, no VBA required.

View/Toolbars and select the Watch Window toolbar. You can then set a
Watch
for various cells.

This is different from the Watch window in VBA.


--ron

Hi Ron,

Tried that but it does not give me anything more than the value in the total
cell and its formula. I am interested in seeing the individual values of the
component parts of a formula as described in my original post. So I can do a
quick audit of the formula based on an assumption sheet or known values.

Thank you.....

David S


Oh. I thought you had tried the Evaluate Formula option suggested by Niek and
found it lacking. But that would be one thing to try.


--ron

Hi. Don't know if you would be interested in this idea:
If I select your equation, and run this macro (via button for me), then I
display a message like the one below.
This only works for simple equations like yours. Doesn't work well for more
complicated equations.
I personally like to make them cell notes. Maybe this will give you some
ideas for your own:

Sub Equation_Values()
Dim v As Variant
Dim itm As Variant
Dim Fx As String
Dim s As String

Fx = ActiveCell.Formula
s = Fx & vbLf

On Error Resume Next
Set v = ActiveCell.Precedents
If Err.Number 0 Then Exit Sub

For Each itm In v
Fx = Replace(Fx, itm.Address(True, True), itm.Value)
Fx = Replace(Fx, itm.Address(True, False), itm.Value)
Fx = Replace(Fx, itm.Address(False, True), itm.Value)
Fx = Replace(Fx, itm.Address(False, False), itm.Value)
Next itm
s = s & vbLf & Fx & vbLf

For Each itm In v
s = s & vbLf & itm.Address(False, False) & ": " & itm.Value
Next itm

'// Usa a Message Box
' MsgBox s

'// Or make it a Comment
Err.Clear
ActiveCell.AddComment s
If Err.Number 0 Then ActiveCell.Comment.Text s
ActiveCell.Comment.Shape.TextFrame.AutoSize = True
End Sub


Displays:
= = = = = = =
=A1+B1+C1/D1

=5+2+3/4

A1: 5
B1: 2
C1: 3
D1: 4
= = = = = = =



--
Dana DeLouis
Win XP & Office 2003


"David Smith" wrote in message
...
Hello,

Is there a way (addin utilities or otherwise) to cause the formula in a
cell to display the values for the component references making up the
formula?

-i.e =A1 *(B2+C4)/D2

would display based on the cell reference values as:

=5*(4+6)/8

Thanks for your help,

David S