How to make a VBA that self-references a cell and adds from another clel

Hi,

Essentially what I want to do is to input a value into cell B5. This value will take the difference of the Actual vs Quota (C4 vs B4), and add it to the cell input I put in.

So the difference is +700. This means that my quota for the next month will not need that +700 since I already hit it. So what cell B5 is doing is taking the input of the cell, lets say 3000, and taking away 700 from it, or adding to it if it was a deficit. This would continue all the way down.

I want to be able to manually enter any number into B5, and all the way down, and it will update accordingly.

Unless you know of a formula that does it but i think it is vba because of self reference

Hi - I'm guessing this is a continuation of this thread https://www.excelforum.com/excel-for...s-entered.html

Trying something like this can have unintended consequences as it works counter to how users expect excel to work when you enter data. Something like the following could work, but needs further thought on how to handle
a) if user changes a value from a previous row, as it won't automatically flow down
b) errors, such as if text is entered accidentally
etc

Please Login or Register  to view this content.

I think the code does what you are describing.

To make it work, make sure it's inserted into the Sheet module under Microsoft Excel Objects (see https://www.excel-easy.com/vba/event...t-change-event)

I've added some explanations on how the code works below

Please Login or Register  to view this content.

To switch the way the quota calculates, change this line:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

There are several methods to have all the cells combine to a desired answer e.g. using adjustment factors. It would help if you can explain in your mind how it would work and we can help advise how to translate the logic to excel