Kiến thức

Excel: Use Table As Data Validation List Drop-Down

Excel: Use Table As Data Validation List Drop-Down

Excel: Use Table As Data Validation List Drop-Down : Excel: Use Table As Data Validation List Drop-Down



Bạn đang xem: Excel: Use Table As Data Validation List Drop-Down

Data validation: How to make a cell to select values from a drop-down

Creating a table in  

MS Excel‌

  can be very frustrating if you repeat the same input over and over again.

At the same time, it is very convenient if you just select the input contents from a certain drop-down list.

Let’s see how to use the validation rule, a function that makes it possible.

Excel: Use Table As Data Validation List Drop-Down : What is a drop-down? A drop-down list is a function that allows you to select the content you want to enter from the downward triangle ▼ on the right side of the cell as shown in the figure. Your selection will be automatically filled in the cell.
What is a drop-down? A drop-down list is a function that allows you to select the content you want to enter from the downward triangle ▼ on the right side of the cell as shown in the figure. Your selection will be automatically filled in the cell.   

The table below is an expense list of an individual. The date, type of expense, and value are entered every time some money is being spent.

Using this table, let’s set column B so that the expense types can be selected drop-down list, and only from there, to avoid setting them up every time, and avoid any mistake in data entry.

Solution 1: Create a dependant drop down list by direct input

Let’s start by Selecting the cell for which we want to set the drop-down list.

Select Data Validation from the Data tab in Excel, it might be displayed as a small icon only.

Excel: Use Table As Data Validation List Drop-Down : Data Validation option in Excel Data tab
Data Validation option in Excel Data tab  

In the settings tab, display the allow drop down option and select the List option. This will allow you to create or select a list of allowed values for selected data cells.

In the source field, simply enter the values you want to allow, separated by a comma.

Excel: Use Table As Data Validation List Drop-Down : Creating a custom validation list by direct input
Creating a custom validation list by direct input  

Simply validate by clicking OK, and your drop-down validation list will have been created for the selected cells.

If you select one of these cells, an icon will appear at the end of the cell, and by clicking on it, the drop-down validation list will be displayed, letting you select the value to be automatically entered in the cell based on your direct input list.

Excel: Use Table As Data Validation List Drop-Down : Drop-down validation list allowed values
Drop-down validation list allowed values  

Xem thêm: Cách đánh số trang trong word 2010 bỏ trang đầu tiên nhanh nhất

Solution 2: Create a dependant drop down list by selecting a range of existing values

Similarly, instead of typing the values manually, you can specify a range of allowed values by selecting a list that already exists in the workbook.

A best practice when creating several drop-down validation lists in  

Microsoft Excel‌

  is to have one or more specific sheets that contains only the various validation lists, to make it easier to find them and update them.

After have created your list of allowed values, in our example on the same worksheet to make it visually more accessible, select the cells on which the Data Validation should be enforced.

Then, open similarly the Data Validation menu from the data tab, select the list type in the drop-down menu, and for the source, instead of entering your own values, click on the button on the right side of the field.

You will be redirected to your worksheet, and all you have to do is to click on the first cell of your Data Validation table, move your mouse to the last cell containing one of the valid values, while holding your click, and release the click only after you have reached the last cell of Data Validation values.

If you have created a table for your data validation values and named it, you can use the table name instead of cells range to reference it

The values you have selected that way will be referenced in your Data Validation list, and you can simply add values to your Data Validation – or remove them – by updating that list.

Excel: Use Table As Data Validation List Drop-Down : Data validation drop-down list created from an existing table
Data validation drop-down list created from an existing table  

Set data validation entries on a different sheet

In order to better organize your workbook, it might be easier to create your Data Validation lists in a specific workbook, so you can easily find and access them, and won’t have to wonder where they are set.

Put your data there, select the table, and use the option tables:table in the insert tab, to transform your list of values into a table that will automatically be rezised as you add or remove values – and so will be your data validation.

Then, click anywhere in your stylized table, and enter a table name in the table design tab.

This will allow you to reference this table anywhere, including in a Data Validation list, by simply entering the table name instead of the range!

Excel: Use Table As Data Validation List Drop-Down : Setting up names of a data validation list in a separate worksheet
Setting up names of a data validation list in a separate worksheet  

Xem thêm: Kiểm tra, đánh giá chủ đề ba định luật Newton theo định hướng phát triển năng lực – VLOS

How to allow blank cells in a data validation list

If you want to allow blank values in a field that has Data Validation enabled, you must select one of the cells for which Data Validation is activated, and go to the data tab, to open the Data Validation menu (see above where the button is).

There, make sure that the ignore blank option is checked, and click on apply these changes to all other cells with the same settings before clicking OK.

You will then be able to empty a cell by selecting it and removing content, without getting an error message specifying that cells content is not allowed.

Excel: Use Table As Data Validation List Drop-Down : Data validation list with blank cells allowed
Data validation list with blank cells allowed  

How to disable blank values in a data validation list

If you do not want blank values to be allowed in a Data Validation drop-down list, and get an error message the value in this cell is invalid or missing whenever you select it, start by select the range of cells on which Data Validation applies, or any cell in that list.

Then, open the Data Validation menu in the data tab, and uncheck the ignore blank field. If you have selected only one cell, check the apply these changes to all other cells with the same settings before clicking OK.

Back at your Data Validation drop-down list, the error will appear – and it will not be possible to leave a cell blank anymore.

Excel: Use Table As Data Validation List Drop-Down : Blank value not allowed in data validation drop-down
Blank value not allowed in data validation drop-down  

Xem thêm: Ước và bội của một số tự nhiên là gì? Ước chung lớn nhất, Bội chung nhỏ nhất là gì?

How to remove a data validation drop-down list

Whenever you are done with limiting the range of allowed values in a list, select any cell in that list.

Then open the Data Validation menu from the data tab – see above in first section how to do it – and make sure that the apply these changes to all other cells with the same settings option is checked, otherwise you will only remove the Data Validation on the currently selected cells.

Then, click on clear all and the Data Validation you have selected will be removed from the  

MS Excel‌

  workbook!

Excel: Use Table As Data Validation List Drop-Down : Click on clear all to remove a data validation drop-down
Click on clear all to remove a data validation drop-down  

VBA: Add validation list

Adding a validation list with VBA is as easy as using below function, where A1 is the cell in which data will be validated against the Data Validation list stored in cells B1 to B5.

Range("a1").Validation _ .Modify xlValidateList, xlValidAlertStop, "=$B$1:$B$5"

By using the advanced properties of the VBA Data Validation list function, it is possible to create multiple dependent drop down lists in Excel VBA that each validate various fields against various Data Validation lists, and have their own titles, error titles, messages and error messages.

 
 

VBA: add validation list manual


Excel: Use Table As Data Validation List Drop-Down : Adding a validation list with VBA
Adding a validation list with VBA  

⋞ How To Change The Language On A Website?

What Are The Best Ezoic Compatible Hosts? ⋟


Chuyên mục: Kiến thức

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Check Also
Close
Back to top button