Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
David Smith
 
Posts: n/a
Default Question On Excel Formulas

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



  #2   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Question On Excel Formulas

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





  #3   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Question On Excel Formulas

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



  #4   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
David Smith
 
Posts: n/a
Default Question On Excel Formulas

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



  #5   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Tom Ogilvy
 
Posts: n/a
Default Question On Excel Formulas

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







  #6   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
David Smith
 
Posts: n/a
Default Question On Excel Formulas

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







  #7   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Tom Ogilvy
 
Posts: n/a
Default Question On Excel Formulas

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









  #8   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Question On Excel Formulas

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
  #9   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
David Smith
 
Posts: n/a
Default Question On Excel Formulas

"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


  #10   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Question On Excel Formulas

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


  #11   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
David Smith
 
Posts: n/a
Default Question On Excel Formulas


"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


  #12   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Question On Excel Formulas

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



  #13   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Question On Excel Formulas

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
  #14   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
David Smith
 
Posts: n/a
Default Question On Excel Formulas

Hi Niek,

Yes, you are correct. This works. Not exactly as I would like, but what the
hey.

I have Excel 97 and 2003. I still use 97 and this feature is not in it of
course, that's why

I did not catch on right away.

Thank you for you help.

Later.....

DAvid S


"Niek Otten" wrote in message
...
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
m...
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





  #15   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Dana DeLouis
 
Posts: n/a
Default Question On Excel Formulas

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





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
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Delete the formulas of webpage, copied & pasted on excel sheet Mustafa Abedin Excel Discussion (Misc queries) 1 June 19th 05 02:39 PM
Provide a way to turn off auto-checking excel formulas as I type t ikarius_rb Excel Discussion (Misc queries) 6 March 23rd 05 01:33 PM
How Excel & ACCPAC 6.1 calculate formulas???? Bass Mama1 Excel Worksheet Functions 1 February 9th 05 04:25 PM
Excel Formulas albebach Excel Discussion (Misc queries) 1 February 2nd 05 10:52 PM


All times are GMT +1. The time now is 06:53 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"