|- Right click the sheet tab.|
- The bottom choice should be View Code (at least in Version 2003 and earlier)
- Click View Code
- A blank VBA Editor window should open.
- Paste the code from my earlier post into the window. (Copy everything from the line that starts with Private to the one that say End Sub)
This is known as a macro, specifically an event macro that will run whenever there is a change to the spreadsheet. The reason you need to use a macro is because you can't put a formula in a cell and then enter a number in the same cell - the number would simply overwrite the formula.
What that particular macro does is this:
1 - Read whatever you put into a cell (the "Target")
2 - Multiply that number by whatever is in A1.
3 - Store the result in a variable called NewNum
4 - Place the value of NewNum back into the Target cell.
The error code is so that the code doesn't bomb if you put text in a cell.
However, the problem you are going to have is that the code is going to run regardless of which cell you enter data in.
So let's say you put a 2 in A1. Since the code doesn't care which cell you changed, it's going to to read the 2, then multiply it by what is in A1 (2) and you'll end up with 4.
So now you have a 4 in A1. Put a 3 in B1 and you'll get 12.
Now in C1 put =A1+B1
While you would like the answer to be 16 (4+12), you are going to be disappointed. It's going to be 64 since the entering of the formula will trigger the change and the answer will be 16 for a split second until the code multiplies it by A1 (4) and replaces the formula with number 64.
So what you need to do is tell the Event macro exactly what range you actually want it to change instead of letting it change every cell in the workbook. Give me some details and I'll see if I can make that happen for you.