Running Balance

Question

I am working on an excel to monitor water tank manufacture.

On sheet 1, I have inventory listings as follows: Polymer Powder, 330L, 1000L, 4000L, 6000L, 10000L, Strainer, Over Flow and Outlet.

On sheet two I have columns: Item, In, Out.

If I have 1000kg polymer power under in column on sheet 2, polymer powder on sheet one should increase. However, if I manufacture one 330L tank using 20kg of polymer power, I want polymer powder on sheet 1 to decrease by 20kg and 330L to increase by 1. Tank number should be under in and 20kg used for production should be under out column.

Please someone help.

Answer 1

Sheet 2 with the in and out will need to include the starting inventory point as "In" figures in order for a formula to include them and then calculate additional In/Out changes. Here is my sample of your Sheet 2. Starting inventory is highlighted in yellow.

On Sheet 1 you can then use this formula to calculate the inventory and keep a running total of that inventory based on additions to the In/Out columns in Sheet 2. It is entered in B2 and filled down for all Items.

Note that the Units, kg or tanks, must be separated from the numbers for Excel to treat the inventory and change numbers as numbers and be able to calculate the running totals.

=SUMIF(Sheet2!$A:$A,A2,Sheet2!$B:$B)-SUMIF(Sheet2!$A:$A,A2,Sheet2!$C:$C)

Answer 2

Hi Jeovany,

Will follow your advice with a sample copy.

Answer 3

Hi there

I hope you are OK

I'm writing to follow up on this thread because we haven't received any news from you in days

May I know if you need further help?

Did you solve your problem?

Did the reply/solution solve your problem?

Any updates will be welcome.

Regards

Jeovany

Answer 4

Hi there

To better help you, please provide a link to a sample copy of your workbook, for us to check and find the appropriate solution to your problem.

If you need help with how to upload the file please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

You may also follow the instructions in this video

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Regards

Jeovany