Inventory Help!

[sean2minu]

Hello Everyone!

I'm a beginner in Excel and I'm trying to figure out how to create a formula for my company's inventory spreadsheet.

-Let's say we have 2 sheets: "Master Inventory" and "Inventory IN/OUT"
-The sheet "Inventory IN/OUT" contains drop down menus for SKU type selection. (Lets just say selections are Product A, Product B, Product C, etc.), there are also 2 columns listed "Inventory In" and "Inventory Out". The purpose of this sheet is to record how inventory is moved in and out.
-The sheet "Master Inventory" has several columns (Warehouse, E-commerce, Container #1, Container #2 and Total Inventory), each column represents the inventory in those respective locations. The Total Inventory column, obviously, is the total of all inventory regardless of location.


What I want to do is set a formula that will utilize the data selected in the drop down menu in sheet "Inventory IN/OUT" to automatically assign the addition or subtraction of inventory to the corresponding SKU's "Total Inventory" column located on the sheet "Master Inventory". I've been researching what would be the best formula for me to do this, but with the complexity of utilizing a drop down menu (which is already set-up with 21 options of SKU #'s) and having whatever data is input into the "Inventory In" and "Inventory Out" columns to automatically adjust the Total Inventory from another sheet... has me stumped...


Please help me! Should I use IF statements? Conditional Formatting? I'm so lost!

Thank you in advance!!

 

[igold]

Hi sean2miu,

Welcome to the forum.

You are not going to like this answer, especially as a reply to your first post, but here goes... As a you are a self-admitted beginner, there are just too many moving parts to trying to create a spreadsheet for inventory control. Something that encompasses Inventory on hand in different locations, Inventory relief and Inventory replenishment by sku number, by location as a live sheet, is a big task.

Perhaps someone on the forum will disagree with me and jump in and help get you going. I think you may not realize the kind of beast this thing can grow into. A lot of people would say that this is better suited for Access.

Just my two cents. I am sorry that I could not be more help, but at least you will get a bump out of it.

igold

 

[FDibbins]

Every journey begins with a single step.

I agree completely with igold, but maybe if we take this in small bites, we can get you closer to where you want to be.

Essentially, you have 2 parts to something like this...
data capture
data analysis

the 2nd part is almost always easier to do, if the capture is done efficiently.

What does your base data look like, and where does it come from?

 

[sean2minu]

Hi sean2miu,

Welcome to the forum.

You are not going to like this answer, especially as a reply to your first post, but here goes... As a you are a self-admitted beginner, there are just too many moving parts to trying to create a spreadsheet for inventory control. Something that encompasses Inventory on hand in different locations, Inventory relief and Inventory replenishment by sku number, by location as a live sheet, is a big task.

Perhaps someone on the forum will disagree with me and jump in and help get you going. I think you may not realize the kind of beast this thing can grow into. A lot of people would say that this is better suited for Access.

Just my two cents. I am sorry that I could not be more help, but at least you will get a bump out of it.

igold

I was afraid of that =( Thanks for chiming in.

 

[sean2minu]

Every journey begins with a single step.

I agree completely with igold, but maybe if we take this in small bites, we can get you closer to where you want to be.

Essentially, you have 2 parts to something like this...
data capture
data analysis

the 2nd part is almost always easier to do, if the capture is done efficiently.

What does your base data look like, and where does it come from?

The data for the inventory in and out will be input manually every time products move in and out of the warehouse. Imgur: The most awesome images on the Internet

Thanks and any help to figure out a better way to do this on excel would truly help!

 

[igold]

Yeah, unfortunately I speak from experience. Good luck!