macro to get formula answers with data

dear friends below formula work fine for me.but i need to get that formula answer with all data.
ex-
80+80=160
but this formulas giving only last answer =160.i need see that 80+80 also.pls anybody can make macro for this.thanks.

B5 cell

Please Login or Register  to view this content.

C5 cell

Please Login or Register  to view this content.

D5 cell

Please Login or Register  to view this content.

E5 cell

Please Login or Register  to view this content.

Originally Posted by johncena

this formulas giving only last answer =160.i need see that 80+80 also

First can we enquire as to why ?
(you can use F9 in the formula bar or evaluate tool to visually see the component parts of the resulting value but I suspect that's not viable here)

In terms of the "Macro" - what are you looking to do ?

Are you looking to replace the existing formula (say with a string of valid results via a formula with + operator) or are you looking to return the various elements comprising the formula result elsewhere ?

If you're looking to replace the formula altogether you have an issue in so far as you would (I think) need to use a Sub routine rather than in-cell UDF ...
In turn this raises issues regards precedent alterations needing to invoke the sub routine
ie you change the source values you need to invoke the macro simultaneously to update the results which could get quite convoluted quite quickly...

All of the above is meant to be interpreted as "is it really worth it" ?


Thinking "outside the box" could you not use a Pivot Table as source for your calcs ? This would also give you drill through detail on the PT itself in terms of investigating results

In terms of PT config - column D would be either Row Field / Column Field, Column E would be the opposite of Col D (ie Row/Column), Colummn G would be Data Field set to Sum.

With the PT configured you can use GETPIVOTDATA function from your B5:E5 cells to retrieve the values, you can drill into the totals on the pivot to see the component parts.

ohh.sorry sir i think not clear my requirement.i need like this.one place have 5 & another place have 10. then answer should =15.if i click that cell then only should display =5+10.other wise it should display only 15.your report is ok.just add "=" to first.
ex-
5+10
should =5+10
pls see this attachment.

OHH thanks a lot for your reply sir.but why u put apostrophe to first.i don't want that apostrophe.i know if we remove that it will calculate,it's ok.i need just calculate all values.it like this ....=50+60+40
cell value is=150 if i click that cell then formula bar should display =50+60+40,
i have copy my yesterday production sheet to your sheet.but it's not giving correct answers.pls check this..

no sir B6 to AL6 all answers i need.but this macro answer giving only B6 to M6 range.other range values(N6:AL6) not calculating.pls check my last attachment.