Inventory Totalling Challenge

Hi there.

Any help from the experts on this one is greatly appreciated.

Want to create a 'running inventory' for several toners that we are using here in our gov't office. (Don't worry, I'm with the good guys part of the government - Corrections .)

Anyway, lets assume after a count throught the institution, there are 10 boxes of HP-96A toners on hand (Cell-J4). (Spreadsheet Attached - light macro is included to pop the current date to the left, just to let you know)

I want to know how to create a formula from THIS POINT ON influences the overall count that J4 will show.

A quantity of 4 HP-96A toners are 'recieved' (D1) and in a SUMIF cell (N3), it is noted in relation.

Now how do I make the J4 cell increase by 4 units?

What happens if, for some reason an independent Toner Audit is done and it is discovered that there are no longer a matching amount of HP-96A toners in the whole place?

Entries into Column E would also cause a 'drop' in the count too - a farming out of the toner would drop the main inventory.

I'm at a loss as to where to begin.

Thanks for any input necessary to remedy my situation/quandry.

Martin B

Why not just use SUMIF, similar to how you used it in columns M and N? If your physical count is 10, and that is what you want to use for your starting point, just use in J4:

=10+SUMIF($B$4:$B$101,I4,$D$4:$D$101)-SUMIF($B$4:$B$101,I4,$E$4:$E$101)

This will take 10, add any quantity received, and subtract any quantity dispensed.

Thanks so much. I'll try that and apply it too other areas of the other worksheets for the other departments.

This place is the best!

Martin B.