Adding row values while keeping the values in a formula statement

Hi guys,


Well, it was even hard to try to find a correct thread title for this one. I have this strange question that some of you can probably help me with:

I have two rows of data, say:

1 2 3 4 5
2 3 4 5 6

And I would like to sum up each data point (on the column) while still keeping the values visible in the resulting formula expression. What I mean is, usually, adding/summing up leads to:

3 5 7 9 11

However, I want it to see the distinct formula such that each added cell says:

=1+2 =2+3 =3+4 =4+5 =5+6 (of course, each cell really shows the values: 3, 5, 7, 9, 11... but these values should have the underlying formula expressions that I noted ;-))

It's a strange question, I know. But say you have a row of data to which you want to add a value to each point (that is, add one row to another). Normally you would simply add the two rows, but then you have to have both the rows somewhere for you to look up, should you have any question as to which value was added to what. In the way I'd like data, I can simply go to the data point and see which two distinct values are added to form the total cell value.

You can probably do a macro in this instance, but my macro skills are rather limited presently, so it would take me some time to code :-p

Also, I thought perhaps some excel function could be helpful in this instance? (For instance, a value (say, "10") added via paste special -> 'values' and 'add' to a referenced value (say, "=B3" pointing to the value "5") gives e.g. "=(B3)+10". So that's not far off... I simply want the B3 value to be expressed as well in "=5+10" (cell showing "15") :-)

True, I know it's a strange question. The story goes that I'm working with an Excel model, and I'm trying to keep it nice and clean without too many lines/references/sheets etc. However, I have a range of data and I need to add a data range to this range (all other data ranges in the sheet are simple values, thus I use the whole sheet as an 'input sheet') - but I'd like to refrain from having both ranges displayed; normally one would simply have both ranges and reference the fact that one range is added to another. But I simply thought that my model would have a rather clean appearence if I could simply add the data together and still see what the actual data point sum is made up of - without having to have two ranges displayed, either in the same sheet or different sheets, via references.

I probably didn't help the matter by writing that story - suffice it to say, it's rather hard for me to describe the background :-p

Anyway, again - it's been a loooong time since I used macros, and I just tried your code above, however I'm not successful in doing so... can you give me a short step-by-step?

E.g. A1:E1 is 1, 2, 3, 4, 5 and A2:E2 is 2, 3, 4, 5, 6 and A3:E3 =A1+A2, =B1+B2, =C1+C2, =D1+D2, E1+E2

What do I do? I thought I had to select A3:E3 and run the macro code (i.e. named 'test') but that results in an error at the 'rngLoopRange.Formula' line?

Also, thanks for helping... it's greatly appreciated - I ought to dabble more with macros! :-)

Ah, got it! - Thanks a whole bunch for your help! :-)