AccurateStock Count

[warby23]

Hi All, I posted this message below a couple of weeks ago but because of work commitments I was unable to repond to any o the replies I had and my post was closed, So I have copied my origninal post below in hope that I get solve my problem this time.

Hi all,

I have created a basic stock list speadsheet that appeared to work at first using the countif statement, but have found one major flaw in it, If I check an item of stock out and then check it back in then that item of stock is counted twice and my total stok count is incorrect (over by 1),

I think I need to use a little bit of a database in my spreadsheet but have never done it before, How can I go about making my spreadsheet count an item only once regardless of how many times I check it in or out, so that I can get a true stock count when needed.

Background to my stock - basically i get a shipment of PC's for one customer that I store in a holding cage, I need to have an accurate count of the PC's at all times. I will take a PC out of the holding cage and work on it, then I return it to the cage (so the stock of the cage goes down by one and then back up by one) this is all tracked by the PC serial number, When appropriate I will take a certain number of machines out of the stock and install them at a site, but again I need to make sure that the total number of PC's (in my holding cage, in my warehouse and out on site) matches my original delivery count.

I have attached a copy of the spreadsheet that I am using, Basically I have been scanning in the serial numbers into the Stock in Sheet and then Scanning them out on the Stock out sheet, you will see what I have tried to explain above when you look at it. Someone did mention that I could use an indicator to show whether or not an item is in or out but I didn't really understand what they meant

Thanks in advance

[shg]

First, get rid of all the space.

Have one column for S/N, another for type (maybe D, L, and S, for desktop, laptop, and screen) and the others (date/name/verified/comment) as now. Stock In and Stock Out should be combined on a single page, and an Action column can indicate removed/returned. When you're done, each line is a 'transaction', and you'll be set to analyze.

[warby23]

Thanks for that, I've gone through what you said and it seems to be working a treat. Amazing what you can do when you at something in a different way.

I really appreciate your help, Thanks again

[warby23]

Hi Again,

After giving my warehoue staff access to the spreadsheet and explaining to them how it works they came back to me with a few questions and I am stuck with some of them.

1. On the Stock worksheet, is there anyway in which you can input an option from the dropdownw list just by using the keyboard (without having to type in the whole word), they want to be able to press te 'l' button and it autoselect the 'laptop' option or press the'd' then 'e' buttons in sequence and it autoselect the 'desktop' option.
2. Is it possible to create a form to input the data on the stock worksheet instead of editing the actual worksheet itself, I am trying to remove the possibility of the staff 'accidently' changing anything on the sheet and breaking it.

I have attached a copy of the new spreadsheet for people to view
Thanks