Simple Inventory sheet

**chris24** · 03-15-2010, 06:01 PM

I need to make a simple spreadsheet to track inventory. Column A is Received, B is Shipped, C is On hand. When a number is entered into A, quantity is added to C. When number is entered into B, quantity is subtracted from C. I need the cells in A and B to be reset to 0 after calculations are made. Simple formulas wont work because the cells will not reset to 0. I need VBA code but I don't no how to write it.

Thanks,
Chris

**teylyn** · 03-15-2010, 06:19 PM

Hello Chris,

welcome to the forum.

It would be very bad practice to reset the data entry fields to zero after the values have been calculated into column C. You'd never be able to figure out if a specific entry has already been made or not.

The usual practice for such a construct is to have a table where you add a new row for each activity. Column C will keep a running tally, columns A and B show you the history of received and shipped items.

A table could look like this

Please Login or Register  to view this content.

After entering the starting balance in cell C2, the formula in C3 and down is

=IF(COUNTA(A3:B3),C2+A3-B3,"")

You can copy this formula down to pre-populate empty rows and it will only do the calculation when either a received or a shipped amount is entered.

You can enter both receive and shipped on the same row.

**chris24** · 03-15-2010, 07:58 PM

Thanks teylyn,

I like having a running tally but my current inventory is setup as follows:

| Item# | Desc | Shipped | Received | On Hand |

I have over 100 items and am not sure your formula, as is, works for my layout. Can a cell be configured to open another sheet so the tally can be recorded? For example, I click on the Shipped or a Shipped/ Received cell for item X? That cell opens a sheet with a running tally like your formula and the results show up in the On Hand cell. Or, something like that.

Thanks,
Chris

**teylyn** · 03-15-2010, 09:59 PM

Chis, you need to separate your inventory table from your booking table.

Have a look at the attached.

**chris24** · 03-17-2010, 12:14 AM

Thanks,
That's what I was trying to do.

**teylyn** · 03-17-2010, 12:59 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save