VBA: Show Subtracted Figure In Cell When Total Figure Is Entered

[hrayani]

Hello Guys,

I am working on a Daily Production Status Monitoring file.

I do get daily figures from the quality controller but only the total figure (accumulated figure) by the end of the day.

For Example

Lets say if production on day 1 is 100 pieces and day 2 is 110 pieces.
for 1st day i will get a figure of 100
for 2nd day i will get a figure of 210 ( instead of 110) which is the accumulated figure.
so on and so forth.

On 1st day i will manually enter 100 in cell A1
On 2nd day i will manually enter 210 (the accumulated figure) in cell A2.
What i want is when i enter 210 in cell A2 then it should convert it to 110. like it should subtract total of the top cells from the current cell value i.e. of 210 (manually entered) and convert the figure to 110 automatically as soon i press enter.

kinldly let me know if its possible ?

Regards,

Humayun

 

[WaterGypsy]

I think your best bet is to have both the accumulated and daily figures on the sheet. Trying to enter a number and then trigger an action to recalculate it is going to prove problematic if you want to change values.

 

[hrayani]

Thanks WaterGypsy

[COLOR=#333333] Trying to enter a number and then trigger an action to recalculate it is going to prove problematic if you want to change values.[/COLOR]

problematic in what sense ? sorry i did not understand

Do u mean that it's impossible ?

 

[WaterGypsy]

No it's not impossible ... it's not even difficult but I'd question the reason why you don't want to see the cumulative figure ... and say you have entered 3 or 4 or the week's totals when you notice a mistake - how are you going to go back and fix it ? And what impact do you want it to have on subsequent days?

Lets say your cumulative figures are 10, 25, 40, 43, 50
So if you enter that and calculate the daily numbers you will get 10, 15, 15, 3, 7

Then you realise that your total for the first day was wrong. You might change to 7. BUT what do you want to happen to the rest of the data ? Will they be changed too ? Or are the old daily figures correct. If you hold all the raw data you will find it easier to see what happens.

SO if you still want to calculate when you enter a value then put the code into a subroutine in the code area of the sheet as follows:

<code class="vb keyword">Private</code> <code class="vb keyword">Sub</code> <code class="vb plain">Worksheet_Change(</code><code class="vb keyword">ByVal</code> <code class="vb plain">Target </code><code class="vb keyword">As</code> <code class="vb plain">Range)</code>
<code class="vb spaces">    .... your calculation here .....</code><code class="vb keyword"><code class="vb plain">
</code></code>
<code class="vb keyword">End</code> <code class="vb keyword">Sub</code>

 

[hrayani]

Thanks WaterGypsy,

Its not that i dont want to see the cumulative figure. Total at the botton of the column will be showing daily cumulative figure.
Only problem is that i do get the cumulative figures from the quality control staff so i have to manually subtract the prior entries to get the days total production.

[COLOR=#333333]Then you realise that your total for the first day was wrong. 
You might change to 7. BUT what do you want to happen to the rest of the data ? 
Will they be changed too ? Or are the old daily figures correct.[/COLOR]

If any correction is made which is highly likely. then rest of the data should not be changed.

kindly if you could provide the code.

 

[hrayani]

Data will be in column H, I & J (Cutting, Stitching & Packing)
cell H5, I5 & J5 for the first day entry and so on

 

[WaterGypsy]

Sorry ... I have had a few attempts at it and can't get it to work

 

[hrayani]

Thanks for your efforts.......

Waiting for some other friends - if they can solve it

 

[RoryA]

Please take a minute to read the forum rules, especially as regards cross-posting, and then update this thread with links to your cross-post(s) in any other forum(s). Thanks.

 

[Ramballah]

is it just me or can this just be easily done with simple excel functions inside the sheet?

For example u put in Cell A1 day 1 which is 10
in A2 u will put next cumulitive which is 20 and then in b2 u put =A2-A1
in A3 u will put the next one which is for example 50 then in b2 u put =A3-SUM(A1:A2)
and so forth?