Is there a way to set up Excel (I’m using 2003) so that if you key a number into a cell that already has a number, that it will add them together instead of replacing the value of the cell with the number keyed in?
For example if there is a 1 in a cell, and I enter a 2, I’d like the value to change to 3, and if I went back to the cell that now has a 3 and entered another 2 it would become a 5, etc.
You could probably set that up with a VB macro, but I wouldn’t suggest it; Excel wasn’t meant to work like that.
What is it you’re trying to accomplish? Why wouldn’t the normal spreadsheet format work?
I agree that Reply (circular user name BTW). You can do damned near anything in Excel if you want. It has its own development environment but that doesn’t mean it is a good idea. In this case it probably isn’t. You need to step back and take a look at what you are really trying to accomplish rather than focusing on that step because it isn’t going to work that well easily.
The simplest solution is just to have a separate sheet that you enter the values into row by row and then display that as a running sum on your target cell on your main page. That is easy and standard design. It also allows for auditing and editing.
Thanks for the replies. I suspected there wouldn’t be an easy way to do this, and I appreciate the alternate suggestions, and I think that will be the route I’ll take.
In this case what I’d be using it for would be keying in values taken from something other than an excel spreadsheet (possibly a printed hard copy) and entering them in the spreadsheet I’m working with. Basically I have a spreadsheet summarizing 11 months worth of data, that I want to be able to update with December’s numbers when they’re in, but that data may come from multiple sources and not all at the same time or in the same format. It would be nice if I could just key the additional data into the cells I want to update, but it isn’t absolutely necessary.
I think just knowing that there isn’t a simple answer is enough for me, and I agree writing a macro or something like that would be more work than it would save.
If you keep a running tally of new data, you’d still get the same sum in the end but with an audit trail (like Shagnasty said). If you have one sheet that you don’t want to mess up with a running tally, you could make it equal another cell in a scratch spreadsheet where you enter the numbers – just think of it as a receipt of your data entry, which is a good idea in and of itself.
Data Entry can be slow in Excel.
A form helps a lot. That way, you can enter the data into one set of fields. Press return and it’s saved. Enter the next record. etc.
Behind the scenes, it’s stored cell by cell. That gives the audit trail.
Forms are based on records. Like Name, Address, phone, sex, birth etc. A collection of data (in this case employee data).
There are many sources online. Here’s a short tutorial. Excel is more limited than Access. But, they are easy to setup.
If you must enter your totals all in one cell…
The cell entry looks like: =45.6+34.4+21.2
So, for a new one press the “=” and then use the keypad adding machine style.
For an existing entry press F2 and you can append to the existing formula, again using the keypad.
It’s error prone, and you need to be watchful and be ready to use the undo (ctrl-z).
Another way to set up data entry is to format cells and lock them. Leaving only your data fields unlocked. Then you turn on Protection using…
Tools, Protection, Sheet (actually by default All Cells are formatted Locked. You only need to format the cells that you want unlocked)
Now when you tab, the cursor can only go into the data fields. You don’t have to take your eyes off your data entry paperwork. You can type, tab, type, tab and get the data in. You’ll never screw up and type over a formula, or an empty unused cell.
After you are done. Go to Tools, Protection, Sheet and turn it off. The cell formatting is still in place. Anytime you want, you can turn sheet Protection on and Enter more data. You can also format & lock the earlier cells with data, so it never gets altered.
The best forms are in Access. You can control the Tab order. Validate data in a field before it’s accepted. All kinds of great stuff. But, sometimes it’s just too much trouble to setup a database, create the form, and then link the database and spreadsheet together.