Today TipsMake will guide you a great excel trick that is managing goods by applying Data Validation function in excel
Video link: https://www.youtube.com/watch?v=qFqD3G-wayE
Introducing goods management tips with Data Validation in Excel
Today I will guide you a trick of managing goods by applying Data Validation function in excel very well!First let me give the example you have a warehouse to manage in the following manner:
For example, you have a warehouse tracking table with 3 columns: "Product type", "Product life", "Product status".To manage professionally, you need to do the following to apply the Data Validation function in excel.
- Select in the "Product type" column, we just select in a drop-down menu, just click based on a list of available products, avoiding manual data entry errors. For example, a list of products of iphone 6, iphone 7, iphone 1000, sony z3 z5, .
- In the "Product lifetime" column, we need a corresponding drop-down menu but will have to meet a condition that is consistent with the "Product type" column.For example, in the product category column is iphone, then in the product life column, the drop down menu will only appear for the corresponding iPhone product generations, for example iphone 100, iphone 1000, .
- In the column "Product status" only need to have 2 types "in stock" and "out of stock" for all different products.
To do this, first you need to create a table as in the above example and follow these steps to apply the Data Validation function in excel.
1. Name the data areas to be included in the Data Validation in excel to manage
Depending on how many regions you need to name the data, here we have areas such as "Product type", "Product status", "iPhone", "Sony", etc. Should create a separate sheet of many different lists for convenient management ofExcel's Data Validation function
- Step 1: Create a data area named "Loaisanpham" to represent different types of products, for example.The purpose of this is to put that "Loaisanpham" area in the "Product type" column to selectthe Data Validation function inexcel.For example:
Iphone
sony
Samsung
LG
Oppo
FPT
You scan the area of the product you just created and right-click, select "Name a Range", name it "Loaisanpham"
- Step 2: Create a data area named "ip" to represent the product life - different iphone models, "sn" to represent different sony models, and the rest depends on you.The purpose of this is to put the "ip" and "sn" fields in the "Product Life" column to selectthe Data Validation function inexcel.For example:
iphone 6
iphone 7
iphone 1000
sony z3
sony z5
sony zzz
haven't done 1 yet
haven't done 2 yet
haven't done 3 yet
haven't done 4 yet
not done yet 5
not done yet 6
-> You scan the life of the product just created and right click, select "Name a Range", name it "ip" for the iphone area and "sn" for the sony region, other regions depending on you .
- Step 3: Create a data area named "tinhtrang" to indicate the product availability or stock status. The purpose of this is to include the "in stock" and "out of stock" areas in the "Product status" column to select the Data Validation function in ex cel . For example:
stocking
Out of stock
- >> here we just need to name the data area as "tinhtrang"
2. Create the Data Validation function in excel with name ranges
- Step 1: Column "Product type" we highlight all and select Data - Data Validation .In the Allow select "List", the source we write the name of the data area we put in part 1. Here we will write is "= Loaisanpham" -> check the resultsof the Data Validation function inexcel.
- Step 2: In the "Product life" column, highlight all and select Data - Data Validation . In the Allow section select " List ", the source part here you have to write an if function to meet the conditions "in accordance with the" Product type "column. For example, in the product type column is iphone, the side of the column Product Life The drop down menu will only appear for corresponding iPhone models, for example iPhone 100, iPhone 1000, . "
- Based on what you do in part 1, step 2 that you write the function, here I will use the if function with the following syntax:
= if (b6 = "iphone", ip, if (b6 = "sony", sn, chualam))
ip here is the name of the iphone product life zone data, similar to sn is the name of the data area of sony product lifetime, chualam is the remaining data areas.
-> Enjoy the resultsof the Data Validation function in excel
- Step 3: In the "Product status" column, implementation is quite simple, like step 1: we highlight all and select Data - Data Validation.In the Allow select "List", the source we write the name of the data area we put in part 1. Here we will write is "= tinhtrang"
----> check the resultof the Data Validation function inexcel