Answers to sum in same cell

this cannot be done with functions

Hi Hero,

You would need to use VBA for this, as a cell cannot maintain a formula and a static value simultaneously. Once you type a number into G2 it will overwrite the formula.

Right-click on the sheet tab you're using and choose View Code. Paste the following code into the VB Editor window (right side, white space) then close the VB Editor window. Whenever you change cell G2 the macro will be triggered. (Technically it will be triggered on any change, but if it doesn't involve a change to G2 it will skip the procedure.)

Please Login or Register  to view this content.

Thanks for this. I had a try and could not find View Code when I right clicked. I'm afraid this is too complex for me. I'll stick to easier methods.

If you right-click on any sheet tab you should see View Code, unless someone has removed that entry from the list using a different macro.

Regardless, you can also press ALT+F11 to open the VB Editor, or open it through the Tools menu (Excel 2003). When opening it using either of these methods, just look at the left hand side once it's open and double-click on the sheet for which you want the code to run, then paste my code into the window (empty white space, likely) to the right of that.

Hi Paul,

That worked. Great.

I tried to drag G2 in Excel so that the formula you told me to past could be moved around to other cells but that didn't work. Is there a way of doing that?

If you no longer want the macro to look for a change in cell G2 (let's say you want it to trigger when you change T7), then change all references of G2 in the code I provided to T7. If you need the code to work on multiple cells (not just G2), then spell out what your requirements are. This code was built based on the specific requirements you provided in the first post.