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

E

Eldraad

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 number
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 i
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 cel
(this+this+this etc). The cell shows the total and when you click o
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

Eldraa
 
W

William

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

[email protected]

| 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
|
|
| ---
|
|
 
M

Max

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

Eldraad

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 th
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 i
took to get the answer...BUT not the formula with the Cell numbers....i
other words...

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

Supervisor wants to see the data that went into cells A1 A2 A3 A4 A5 A
and A7 like this...
(I am just throwing numbers here...each cell would have a specifi
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 NO
the formula =sum(A1:A7)


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

Eldraad

Is there some way I could take the value of each cell and add the plu
sign and still have the cell show the total? Maybe in a text forma
with CONCATENATE or something? If I have to rewrite each formula use
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 anothe
formula saying the cell value is now a text then Concatenate adding th
cell and a + sign between each cell...somehow showing the total for al
of this and, when clicked on, the total shows all of the cell amount
connected by plus signs...and, and...explodes in the supervisers fac
ending my problems!!!

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

lol
 
E

Eldraad

Hey Max!

Finally figured out what you were telling me...

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

Max

Eldraad > said:
My question is...will it stay that way when the
"Answer line" is copied to another worksheet?
Click to expand...
.....

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

William

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 "Tools>Options>Edit" 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

[email protected]

| 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.
|
|
| ---
|
|
 
E

Eldraad

Okay Max, you win the Gold Cigar!

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

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

Thanks to everyone who replied and special thanks to MAX
 
E

Eldraad

I may have spoken too soon Max. It works but NOT the way he wanted.

Hard to describe but...

In order to see the numbers that led to the results we have to hit F
for each cell in the formula. Once we have done that we see th
desired:

=40+10+56+18 etc.

seeing as we have a few hundred totals he wants to be able to check i
is impractical to then click undo for each step to revert the formul
back to normal. And, when the row (all the totals) are calculated w
copy that row into a different worksheet. That part is fine when i
shows the +40+20+ etc. But then we have to REBUILD the formula fo
each total...a nightmare.

And the numbers that lead to the totals will be different each day s
we can not get it back from the same worksheet.

Possibly there is a way to copy the row (before F9ing the formulas fo
the desired format) and run a macro on that row that will convert th
formula for each total into the desired =40+20+18 etc format?

We could easily hand copy the row and then run a macro on it and the
SAVE the new row to a worksheet where the daily totals go for eac
month..IF I were not new to excel. My macros are a bit less tha
pretty (though they get the jobs done) and I am not sure how to write
macro to convert the stuff into the desired format.

Sorry, I'm kicking this question back into play...

Eldraa
 
M

Max

Think it'll be worth it to delve into these 2 links ..

a. John Walkenbach's Sub ListFormulas() at:
http://j-walk.com/ss/excel/tips/tip37.htm

It's described there that the macro will insert a new worksheet,
then create a list of all formulas and their current values

b. JE McGimpsey's Sub ListFormulasInWorkbook() at:
http://www.mcgimpsey.com/excel/listfunctions.html

JE's macro will create a new worksheet in the ActiveWorkbook
that lists the functions in each sheet, their location and their current
value
 
E

Eldraad

I guess I am tired. Nothing is sinking in anymore.

formula in cell b22 looks like =b21+b20+b19+b18

the cells listed have these values
b21=5
b20=7
b19=3
b18=27

need to get the total (42) and when it is clicked on it shoul
display...
=5+7+3+27

AND when the numbers in the cells change so should the ones in th
"answer".

is it at all possible to get the numbers to display like this? eve
with an absurdly long calculation? my personal skills (non-existant
were lacking in trying to make a macro tp do this even from the tip
page Max provided.


Thank s again, I...zzzzzzz
 
M

Max

Sorry, I'm out of suggestions how to do it in the way described.

Perhaps others might step in with some ideas for you.

Good luck ..
 
A

AlfD

Hi!

My sympathy!

Maybe best not to get too technical: seems to me your supervisor want
some visual confirmation that the answer is indeed based on the number
he expects to see (or not...).

I would look at putting hidden columns alongside the final sum. So, F
contains the sum(A1:E1) where A1 to E1 get their values from whereever
Put a simple button-controlled macro in so that he can view the 5 hidde
columns A to E and then put them away again. If he wants actually to se
plus and minus signs in there, then I would concatenate string version
of the numbers and likewise hide/unhide.

Al
 
E

Eldraad

Heh heh heh....

Some light at the end of the tunnel...

My supervisors boss asked him why he needs to see the numbers. "T
know they are right if someone asks a question" he replies.

"I see, has the automatic report been run for a week or so and is i
accurate?" asks his boss.

"Well, yes..".

"Run it for another week or two and compare the results. If there ar
questions you guys can dig out the reports and show whoever is askin
the questions the numbers from there".

Now the supervisor is grumping about comparing the number to make sur
the "Report is doing it right...what if there is a problem, how do w
know it got the right numbers?"

I said he could change the formulas every day by copy and paste (afte
F9ing each part of each formula) and save it to a special file OR w
could save an electronic copy of the files the report get th
information from for him to look through (already have them in har
copy form).

Suddenly I have a lot of extra little "fussy work" to do....LOL!

Thanks everyone for your help
 
Top