How would you show all the cells added with plus signs?

I have Supervisor at work who wants a report automated (which I did).
The problem is, the supervisor wants to SEE the steps or the numbers
that the formula used to reach each total.

What we have is:

=sum(a1:a47) as the formula

and

1557 as the answer

What the Supervisor wants to see when he clicks on the total is
something like...

100+400+50+50+800+100+50+7

Just the way you would see it if you typed all the numbers into a cell
(this+this+this etc). The cell shows the total and when you click on
it it shows the steps in the formula bar.

I do NOT think it is needed, BUT, the supervisor says "Yes, it is".

Is there a way to do this?

Thank you for your time and trouble in reading this question

Eldraad


---
Message posted from http://www.ExcelForum.com/

Place the following into a general module

Function TheFormula(c As Range)
Application.Volatile
TheFormula = c.Formula
End Function

Then in the worksheet enter into those cells where you want to show the
formula...
=TheFormula(A1)
[amending A1 to point to the relevant cell where the formula is]

--
XL2002
Regards

William



"Eldraad " wrote in message
...
| I have Supervisor at work who wants a report automated (which I did).
| The problem is, the supervisor wants to SEE the steps or the numbers
| that the formula used to reach each total.
|
| What we have is:
|
| =sum(a1:a47) as the formula
|
| and
|
| 1557 as the answer
|
| What the Supervisor wants to see when he clicks on the total is
| something like...
|
| 100+400+50+50+800+100+50+7
|
| Just the way you would see it if you typed all the numbers into a cell
| (this+this+this etc). The cell shows the total and when you click on
| it it shows the steps in the formula bar.
|
| I do NOT think it is needed, BUT, the supervisor says "Yes, it is".
|
| Is there a way to do this?
|
| Thank you for your time and trouble in reading this question
|
| Eldraad
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

=sum(a1:a47)

Perhaps one way to "see" the components inside SUM()
would be to select the "a1:a47" carefully inside the formula bar
and then press F9 key

The components will then appear like: {1;2;3; .....}

And now* if you press Enter key,
the formula will 'freeze" in-cell as: =SUM({1;2;3; .....})
which would allow your supervisor to examine
the components totalled by the SUM()
to his/her heart's content bg

Click Undo to revert back to previous state

*If you want to exit instead, press the Esc key

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik atyahoodotcom
----
"Eldraad " wrote in message
...
I have Supervisor at work who wants a report automated (which I did).
The problem is, the supervisor wants to SEE the steps or the numbers
that the formula used to reach each total.

What we have is:

=sum(a1:a47) as the formula

and

1557 as the answer

What the Supervisor wants to see when he clicks on the total is
something like...

100+400+50+50+800+100+50+7

Just the way you would see it if you typed all the numbers into a cell
(this+this+this etc). The cell shows the total and when you click on
it it shows the steps in the formula bar.

I do NOT think it is needed, BUT, the supervisor says "Yes, it is".

Is there a way to do this?

Thank you for your time and trouble in reading this question

Eldraad


---
Message posted from http://www.ExcelForum.com/

To clarify the situation...

I have 5 worksheets where reports have been imported.

I have one worksheet that shows totals or amounts from sections of the
five worksheets.

the formula used looks something like this...

=sum(a1:a7)

This will give a total of the values in cells A1 through A7 (say 1500)

1500 shows in the cell.

The supervisor wants to click on the answer (1500) and see the steps it
took to get the answer...BUT not the formula with the Cell numbers....in
other words...

cell looks like this 1500 and when you click on it you see
=sum(A1:A7)

Supervisor wants to see the data that went into cells A1 A2 A3 A4 A5 A6
and A7 like this...
(I am just throwing numbers here...each cell would have a specific
number and all would add up to 1500)

supervisor wants to see this...

10+20+50+40+etc Or, the numbers in each cell added together and NOT
the formula =sum(A1:A7)


When the report is done, we are just copying the line with the answers
and putting them in a monthly log. So, he wants to be able to verify
the correct numbers went in against the reports at a later date.
Driving me nuts.


---
Message posted from http://www.ExcelForum.com/

Is there some way I could take the value of each cell and add the plus
sign and still have the cell show the total? Maybe in a text format
with CONCATENATE or something? If I have to rewrite each formula used
just to get this result that will be okay...I guess.

So cells A1 through A7 have numbers in them and I use TEXT in another
formula saying the cell value is now a text then Concatenate adding the
cell and a + sign between each cell...somehow showing the total for all
of this and, when clicked on, the total shows all of the cell amounts
connected by plus signs...and, and...explodes in the supervisers face
ending my problems!!!

huff huff huff...pant, wheeze.....

lol.


---
Message posted from http://www.ExcelForum.com/

Hey Max!

Finally figured out what you were telling me...

It DOES show the value of each cell in the formula if I highlight each
part of the formula. My question is...will it stay that way when the
"Answer line" is copied to another worksheet? I will try testing
now...very tired.


---
Message posted from http://www.ExcelForum.com/

"Eldraad " wrote
.....
My question is...will it stay that way when the
"Answer line" is copied to another worksheet?
.....

Yes, provided you paste as "formulas", i.e via:
Copy Paste special formulas ok

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik atyahoodotcom
----

I can think of 2 other possibilities.

use the "Trace Dependents" option from the formula auditing toolbar which
will show those cells that relate back to the active cell. [It will not show
those cells which are located on another worksheet.]

Go to "ToolsOptionsEdit" and remove the check mark (if there is one) from
"Edit Directly in Cell". Then you can double click on a cell with a formula
and all the immediately dependent cells will be selected. So if you double
clicked a cell with a formula that had =SUM(A1:J1) then cells A1 to J1 would
be selected.

--
XL2002
Regards

William



"Eldraad " wrote in message
...
| To clarify the situation...
|
| I have 5 worksheets where reports have been imported.
|
| I have one worksheet that shows totals or amounts from sections of the
| five worksheets.
|
| the formula used looks something like this...
|
| =sum(a1:a7)
|
| This will give a total of the values in cells A1 through A7 (say 1500)
|
| 1500 shows in the cell.
|
| The supervisor wants to click on the answer (1500) and see the steps it
| took to get the answer...BUT not the formula with the Cell numbers....in
| other words...
|
| cell looks like this 1500 and when you click on it you see
| =sum(A1:A7)
|
| Supervisor wants to see the data that went into cells A1 A2 A3 A4 A5 A6
| and A7 like this...
| (I am just throwing numbers here...each cell would have a specific
| number and all would add up to 1500)
|
| supervisor wants to see this...
|
| 10+20+50+40+etc Or, the numbers in each cell added together and NOT
| the formula =sum(A1:A7)
|
|
| When the report is done, we are just copying the line with the answers
| and putting them in a monthly log. So, he wants to be able to verify
| the correct numbers went in against the reports at a later date.
| Driving me nuts.
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

Okay Max, you win the Gold Cigar!

I altered the bormulas like you suggested by double clicking each cell
name and then hitting F9 each time. I will have to change some of the
formulas (the ones where it says A1:A7 will have to become A1+A2+A3
etc) but the numbers stayed in place after cutting and pasting to
another sheet.

Pity...I was hoping the supervisor was out of luck the
spoiled....grrrr...

Thanks to everyone who replied and special thanks to MAX!


---
Message posted from http://www.ExcelForum.com/

You're welcome !
Glad to hear you got it working ..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik atyahoodotcom
----