Formula to type in a cell and get it to subtract that from another cell & give the result!

[Kwold]

I hope I explain this correctly.

I am looking for a formula that allows me to Type into a cell a number that then subtracts that number from another cell, but gives me the result in the cell I typed the subtracted number in.

EXAMPLE

Column A Column B

10 (Type in the number 2) 8

Is this even possible?

[jason.b75]

It's not possible with a formula, but it is possible.

Right click the sheet tab, then select view code. Copy the code below and paste into the code editor.

Please Login or Register  to view this content.

To prevent the code working in places where you don't want it to, it's restricted to work only in B2:B20, subtracting the value entered from A2:A20.

[Kwold]

So if I have the amount in the F Column then it should read ("F2:F20") Correct?

[jason.b75]

That is correct, it should be the range that you're going to type into.

Similarly, in the line,

Please Login or Register  to view this content.

The value of -1 refers to the position of the column with the value to subtract, negative values move to the left, positive to the right, so -1 means 1 column to the left.

[Kwold]

Getting there! What I am getting now is a negative number if I type zero in and I would like to get the same number. If I were to type in a 10 I would like to get 10 less than the number.

EXAMPLE
Current situation

Column G Column H
75 (type in 10, Get)-65

Need Situation
Column G Column H
75 (type in 10, Get) 65

Hopefully that makes sense. Let me know.

[Kwold]

I figured it. I greatly appreciat your help.

Now if I could get it to hold the Accounting Setting and get it to quit changing it to currency, that would be perfect.

[jason.b75]

The columns get mixed up in your examples.

You're typing 10 in column H, to get a result of 65?

Please Login or Register  to view this content.

Edit:- Code above is edited to force accounting format on the cell.