Autosubtracting a value from same cell a manual entry is made

Hi tony324 and welcome to the forum,

Let me try to explain what's happening.

In a single cell there can exist a formula or a number. The formula might show a number but it is really a formula in the cell. If you have a formula in cell A1 like = A1 - 2.1, Excel will complain and say you can't do that (circular referencing) because formulas need a typed in number to work with.

So lets fix your problem. In A1 put a NUMBER like 200. Now in B1 put a formula like = A1 - 2.1 and press enter. This will show the answer you are looking for.

This seems easy enough but you want too much.

A work a round. How about having two identical looking tables on two different sheets. On Sheet 1 is where you put youir "Real" numbers and on Sheet 2 you have your formulas.

So if on Sheet1 in Cell A1 you have 200, on Sheet2 in A1 youir formula would be =Sheet1!A1 - 2.1
This method will work but will take two sheets.

Another alternative would be to write VBA Event Code looking at each cell on your sheet. Whenever you entered a number it would grab that number and do the math and stick that number back in the cell. If you were to put a space after an already entered number it would do the math on it again, and again and again.. I don't think you want to do Events in VBA.

I hope this helps.

Hi again,

Using watersev's code, put a number in a cell and press enter. It really does subtract 2.1 just like you wanted.

Now select that cell again and put the cursor up in the formula bar and press enter. Whoops, it subtracted 2.1 again. Did you want it to do that? If you did then great!

@MarvinP a bit strange way to insert values into a cell through formula toolbar .... usually users input everything directly in the cell however that's a weak spot for this particular idea

Not only entering from the formula bar but copy and paste the value to another cell. Also try autofil by dragging the entered number down.

All of a sudden the "normal" excel methods seem to change.

I like your code, and it works but I think an understanding of circular references was the topic needed for this user.

BTW - Where did you learn [b21] = Cells(21,"B") = Range("B21") ? I like Cells but could get used to the Evaluate "[ ]" method as it is easier to type.

please check attachment, the second sheet done as well.

Originally Posted by tony324

Side note, how long does something like this take you to write out?
It depends on the writer
and what exactly is the process your doing called?
It's worksheet_change event triggered by changes on this particular sheet
i know how to access the codes?
Press ALT+F11, CTRL+R, on the left you will see the workbook name, sheets and modules.
(if thats what they are called) by pressing alt F11, but how does one learn what to put in that screen.
There are many books on that as well as online courses.
i am really amazed by all of this and i hope to learn just a fraction of it some day.

@MarvinP: I can't remember exactly, it was in someone's code in the Internet