inventory spreadsheet for a newbie

Hello all! I have recently been charged with inventory control at my new job. Problem is that, well, there is no inventory management system really in place. (i guess post-it notes with numbers on a box might count) I havent messed with spread sheets in a loooooong time so I am a little hung up.

I was wondering if there is a simple formula to help me. This is what I want to do

col A is item description
col B is quantity recieved
col C is quantity shipped
col D is total qty on hand


as a bonus it would be nice to also be able to automaticly flag an item when its time to reorder.
ex I have 100 units on hand and the reorder level is 10 units left. when an item reaches 10 units it places a flag in a reorder col. is this even possible?

The first step would be to get the raw data into the spreadsheet. From there, look into conditional formatting to easily mark cell the color red, for instance, once it gets below a certain level.

I'm not sure what types of formulas you would need based on this information. Maybe you can provide more detail of what you are planning on using the spreadsheet to calculate.

Hi There,

Have you used Access before? if not, it'll probably suit your application much better and there are loads of Access inventory database templates out there to download too.

Just a suggestion

Regards
Carl

here ya go. the general goal is to be able to enter the quantity in or out of widgets and have that count reflected in the on hand col. the reorder level is 100. as the sheet shows the on hand to be 75, there is a mark in the reorder col. I hope that this explains what Im trying to do. I subscribe to the KISS method

ive never played with access at all. I am trying to make this as simple as possible for the Cheif supply officer to access and understand as she is NOT TECH SAVY at all. she barely knows how to operate her i pad. lol. I figgure I could print out a spreadsheet for her with a mark in the reorder col. she can easily just put that on her order list.

Ha ha, I see!

Not to worry though between us all, I'm sure we'll all sort something out for you!

Regards
Carl

@fdibbins now were talking. the formula you provided is working out kinda like I was imagining.

@everyone else.. I am horrible at explaining things so... The goal for this spreadsheet is to help me keep track of inventory in and out and monitor on hand counts and flag when they get low. I was thinking that when I get a shipment of widgets in i would input that number in qty in and it would add to the on hand number and then the qty in field would reset. same with the qty out. field. when the on hand amount reached a preset level(100 in this example) an X would appear in the reorder col. when the on hand level went above the preset level (100 in this example) the x would be removed from the reorder col.

I hope this clears up the goals Im trying to achieve. im sure that access is a much better option but I havent played with that and everyone at the office uses either an Ipad or tablet of some sort and is always on the go. I am trying to make a spreadsheet that the CSO can pull up on her ipad or phone at a glance and see what items need reordered. I guess I will have to learn access sooner or later but for now this will have to do. In just a year this company started with a store and not a clue in the world and now they are opening their 4th store in a year. Better get this warehouse inventory thing under control now rather than let it totally blow up.

@brendan. that will work just fine indeed. The only thing is that I i cant see the functions...It all works great but I cant seem to view the functions in the individual cells. hmmmmm.. I would love a little explanation of how this all works as well. I thirst for knowledge ha ha!

so would I be able to apply the formula =B+C-D to apply to the whole on hand col? or would I need to go through each row and edit the formula accordingly? (=B3+C3-D3 then =b4+c4-d4 ad nausium) or is there a way to apply that formula to that whole coloum so that each row would have its own calculation?

sumif?? ok now my brain hurts. so I could replace the formula =B3+C3-D3 with sumif b+c-d and the on hand coloum would be calculated for each product? Sorry im being dense here

Ahh I guess I should have re read the thread. LOL

so if I copied your formula from #7 down I would have to change only the a3 in your formula to a3.a4.etc to calculate the new value. then I would have to copy e2 formula all the way down? I am trying to get a sheet that looks like the attached.