Reducing Balance Formula

[tcorcoran15]

Hello All,

I am trying to create a formula to look at the following:

On one tab ('Purchases') I have an amount of product pre-purchased.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Balance[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]500,000[/TD]
[TD="align: center"]X[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]250,000[/TD]
[TD="align: center"]X[/TD]
[/TR]
</tbody>[/TABLE]


On another tab ('Product Sold')I have a list of all the products used as follows:

****** id="cke_pastebin" style="position: absolute; top: 192.8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Balance[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]500,000[/TD]
[TD="align: center"]X[/TD]
[/TR]
</tbody>[/TABLE]
</body>[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Reference[/TD]
[TD="align: center"]Amount[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Alan[/TD]
[TD="align: center"]380,000[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]120,000[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Jack[/TD]
[TD="align: center"]180,000[/TD]
[/TR]
</tbody>[/TABLE]

As there has been 680,000 sold, I would like the balance in the first column on 'Purchases' tab to read 0 and then in the second column, I would like it to read 70,000 as this element is still unsold.

Thanks in advance for any help.

 

[tcorcoran15]

****** id="cke_pastebin" style="position: absolute; top: 192.8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Balance[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]500,000[/TD]
[TD="align: center"]X[/TD]
[/TR]
</tbody>[/TABLE]


The above section should be ignored, I am unsure why it has pulled through like this.

 

[mole999]

****** id="cke_pastebin" style="position: absolute; top: 192.8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Balance[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]500,000[/TD]
[TD="align: center"]X[/TD]
[/TR]
</tbody>[/TABLE]


The above section should be ignored, I am unsure why it has pulled through like this.

Its related to an editor often seen with chrome