Inventory management

Hi everyone,

This may be a bit of a complex problem, but I figure it's worth a shot to ask some experts, instead of piddling around on my own with my limited knowledge.

I run a small business and wat to utilize Excel 2010 for my inventory tracking needs. First of all, I want a comprehensive sheet with all my inventory records (stock levels, item number, etc.). This is no problem by itself. What I want ultimately is a way for the stock levels on this aforementioned master inventory database to adjust its stock levels on each inventory entry when I enter that entry's item number on a separate sales sheet. Furthermore, regarding the sales sheet, when I input an entry into the sales sheet, I want Excel to autofill relevant information from the master inventory sheet, such as supplier info, unit cost, etc. to the sales sheet.

For example, let's say I have my master inventory sheet made, and I sell item X. When I put item number X into a cell on a sales sheet, I want the entry for item X to adjust (e.g. subtract 1 stock from item X's stock level) on the master inventory sheet accordingly. Simultaneously, I want the sales sheet to automatically draw pertinent information from Item X's entry on the master inventory sheet and put it in the relevant cells on the sales sheet.

I apologize if this doesn't make sense, but I would be IMMENSELY grateful if someone could tell me (1) if this is even feasible with Excel 2010 and (2) explain how to go about creating such a sheet (what formulas to use and what not).

If anyone is interested in helping and needs further details, feel free to let me know; I can even draw diagrams if needed!

Thanks in advance!

Here's a quick example of some formulas that might work for you. In the attached worksheet on the Master Inventory sheet, the sales column contains a SUMIF formula. On the Sales sheet, the Item number and quantity are entered and all the other data is pulled from the Master Inventory sheet with VLOOKUP formulas. I used a nested IFERROR formula so you can fill the column ahead of time without the #N/A errors showing.

First of all, thank you so much for this; I'm amazed that you knew almost exactly what I was looking for! This is brilliant.

I do have a few more questions though. Is it possible to have a sales sheet that is on a completely different file that can refer to the master inventory sheet (instead of both the sales sheet and the master inventory sheet being in one file together)? I ask because I make a fresh new sales sheet for each weekend, and this would make things a bit easier still.

Also, when I delete the existing values on the sales sheet and then reenter them, the functions don't automatically fill in the information anymore. I knew this would be an issue, I'm just wondering if there's any way to maybe set the cells on the sheets to always have the proper formula formatting, instead of always having to use the little autofill box in the bottom right corner of the cell to do it. This is more of a minor issue, but if there's a fix it'd be great.

Again, thanks so much for the help, this will greatly increase my efficiency!

Yes, they can be in two separate workbooks. The formula would just need to include the workbook and worksheet names.

I think you could solve the sales sheet issue by only clearing the Item and quantity fields. Leave the VLOOKUP formulas in place for the next time.

Okay, great, I've figured out how to use two separate files; will the formulas that connect the two files work even if I move one of the files?

Also, I seem to have run into another sort of snag: If I make multiple sales sheets from which the master inventory sheet gathers data, is it possible for the master inventory sheet to take several sales sheets into account when it's adding up current inventory, sales, etc? For example, I have my single master inventory sheet. If I make a sales worksheet for this specific weekend, would I be able to make a new sales worksheet NEXT weekend, with the master inventory sheet to summing the sales/inventory from both of those sales sheets, or would I have to put all the sales on one sheet? I'm trying to have the master inventory sheet constantly update itself each time I record a sale on a sales sheet, thereby always keeping track of inventory. Id this a good way to go about getting this result?

Once again, thanks so much for the help; I know you're not obligated to assist me, so I really appreciate it!

Sure, you can add as many workbooks as you care to keep track of... Okay, there is a limit, but you won't bump into it for a while.
Combine your countif statements in a sum statement, something like this:

=SUM(SUMIF([Main_sales.xlsx]Sales!$A$2:$A$100,B2),SUMIF([Weekend_sales.xlsx]Sales!$A$2:$A$100,B2))

Hi

Currently I have a sales spreadsheet consisting of products sold, dates, price. And I have a separate spreadsheet on inventory reflecting in columns the months, in rows what has been ordered and sold which I manually maintain - which I now find it quite cumbersome as I have a number of products.


I need some help in setting up a better sales, inventory excel spreadsheet. At the moment, with my limited knowledge I list everything out. What I would like to incorporate is to see when (dates) the sale of my inventory has occurred as a reporting system when I look at my stock.

What formula can assist from a sales spreadsheet or inventory spreadsheet?

Thank you

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

I am looking to do something similar - i would like an excel doc that will let me enter inventory, and then keep track of rentals out of that inventory - using date availability. For instance: CLIENT "A" wants to rent ITEM "X" from May 1 thru May 5, I would like my available inventory to note that ITEM "X" is unavailable on May 1 thru 5 so that I do not try to rent ITEM "X" out to CLIENT "B" at the same time. Is there an existing excel doc/template that will let me do this? and if not, what formulas would I use to create this? I have been using a very expensive off the shelf system and have found that it is just too much info with not enough flexibility. OR, if you know of a program out there that does this (not utilizing Excel) Can you recommend...Any suggestions would be greatly appreciated.