Related Data Validation Lists
A very cool use of data validation is making related data validation lists. If your worksheets includes multi level data entry, this is a very nice way of handling things.
Lets assume that we run a mobile phone sales business and we are frustrated with writing or typing invoices every time. Therefore we are looking for a fancier way of writing invoices.
Recently we have two brands in our inventory. We sell three models of both brands. So we need to set an invoice template that will let us choose a brand, than a model, specify the quantity of phone sold and it will do the rest.
Now that we now what we need, lets start making it:
We need to set our template first:
Now that our template is set, we need to arrange which cells will behave which way. For our example:
- Brand: Brand selection from drop down list.
- Model: Model selection from drop down list, based on selected brand.
- Unit Price: Will be fetched based on selected model.
- Quantity: Will be inputted manually.
- Total: Will be calculated by formula (Unit Price x Quantity)
To make drop down list, we need to arrange some data as shown below:
I named A14:B14 as Brands, A14:A17 as Apple and B14:B17 as Samsung. Now we will select all the cells in Brand column in our invoice and apply data validation. Make it list and use A14:B14 as source. Now our brands column will let us choose between Apple and Samsung.
For Model column, we want to select phone models based on selected brand. This means we need to make related data validation lists. To do this, select all the cells in Model column and apply data validation. Select list and set the source field as shown below:
Check for the result, you will see that model list updates as you change brand. From here it is fairly straight forward. Use a VLOOKUP formula to fetch unit prices, write a unit_price*quantity formula into total column. Do some more formatting if you feel like.
Now you can easily fill your invoices with minimal effort.
You can download and check the example workbook for this tutorial for better understanding the concept.