Inventory Database

[ktkreeger]

I need help thinking out the best way to setup a new inventory system. I would like it to be easy for everyone to use. First thought was Access but I need to take a course or two to figure out how to use it again. I am good at excel, but I am having problems with this one.

We have 100+ clients. Each client has inventory in our warehouse. Every day we write down how many items went out to our client on a piece of paper and subtract that number to get a balance inventory. When we run low, we order more on a new FO number. What is the best approach for tracking this? I was thinking one way is to put our clients in a drop box at the top of the page and then the data will appear at the bottom, however I can't get that to work. I am trying filters to see if that helps, but I am unable to subtract the balance automatically from the last FO# and from the correct area.


Is there a way to choose a client and product at the top of the page and have the tracking below? Anyone have examples of how I should do this?

Thanks for helping and pointing me in the right direction. Attached is a copy of what I am talking about.

[shadrack]

Please confirm if this is what you want. If you need further explanation, do not hesitate to contact me.

[MarvinP]

Hi ktkreeper,

Find attached an example of how I'd do this problem. I'd first put all the transaction amounts in a single column. PLUS if they were coming in and MINUS if they were being shipped out. Then I'd do a Dynamic Named Range of the continuing data. Look in the Names Manager for a DNR of Customers (I think). This range will grow based on how many rows of data you have. Then base a pivot table off this range and update it whenever anything changes. See the very small amount of VBA code behind sheet1. All this gives an updated Pivot if/when anything changes on the inventory sheet.

See the attached for my example.

[newdoverman]

I would treat this like a "Bank Book" where you have "Credits" and "Debits".

The balance can be combined with a table of minimums or Order Points to replenish the stock on hand. This would be done by using the balance for each product as the lookup value against the Order Point table. This can be done like the Bakery example.

[ktkreeger]

Thanks for the help shadrack, marvinp, and newdoverman. All of your examples were great and helped me move along in this process. I found another thread that explains what I am looking to do, however I am using a Data Table and I'm unable to make this happen. I'm getting errors because I'm using the table name and references instead of the ($A4). Here is the thread FIFO Inventory Method

I like what shadrack provided and marvinp, I feel like this will allow me to have a combination of both what you said plus the new thread idea of FiFo. I just need help on the formula. Attached is my updated sheet that shadrack provided with updated data.

Thanks for the help.

[ktkreeger]

This is my formula, but I'm getting an error with the DataTables highlighted in red below.

if($E4>$G$1,0,MIN(N($F4),max(0,SUMPRODUCT( --($B$4:$B4=$B4)*(E$4:$E4<=$G$1),(F$4:F4))-SUMPRODUCT( ( 'Data Entry'!(DataEntry[Item]=$B5)*( 'Data Entry'!(DataEntry[Date]>=$E4)*('Data Entry'!(DataEntry[Date]<=$G$1)*'Data Entry'!(DataEntry[QTY Shipped]))))