If you use the pull-down entry function in Excel with the default settings, you will get an error like the one shown below when you try to enter an item that is not in the list prepared for entry.
In such cases, it is possible to set whether any item can be entered, or whether it can be entered after a message is displayed. In this issue, we will show you how to do this.
How to create a pull-downCreating a pull-down (drop-down) Excel (Excel).
Allow input of values not in the list
To change the drop-down functionality, open the "Data Entry Rules" with one of the cells you wish to change selected. If you want to change the settings for multiple columns, just select one cell in any row.
Next, click on the "Data Entry Rules" (button shown below) in the "Data" tab of the toolbar.
In the "Settings" tab of the menu that appears, select "Apply changes to all cells with the same input rule".without forgettingCheck the box. If you make changes without checking this box, the cell will be excluded from "all cells with the same input rule" and it will be difficult to correct it later.
Next, in the "Error Messages" tab, uncheck "Show error message if invalid data is entered" and click the "OK" button.
Now you can enter values that are not in the list.
Allow the user to choose whether or not to enter the information.
To change the drop-down functionality, open the "Data Entry Rules" with one of the cells you wish to change selected. If you want to change the settings for multiple columns, just select one cell in any row.
Next, click on the "Data Entry Rules" (button shown below) in the "Data" tab of the toolbar.
In the "Settings" tab of the menu that appears, select "Apply changes to all cells with the same input rule".without forgettingCheck the box. If you make changes without checking this box, the cell will be excluded from "all cells with the same input rule" and it will be difficult to correct it later.
Next, in the "Error Messages" tab, make sure "Show error message if invalid data is entered" is checked.
Then change the "Style" item to "Attention" and click the "OK" button.
The "Title" and "Error Message" fields are optional. There is no difference in functionality if you do not enter them.
If you try to enter an item that is not on the list in this state, an error message will appear as shown in the figure below.
Clicking "No" or "Cancel" will return the entry to its original state without confirming it. Clicking "Yes" will allow you to enter an item that is not on the list.
Display a confirmation message to allow you to enter the information.
To change the drop-down functionality, open the "Data Entry Rules" with one of the cells you wish to change selected. If you want to change the settings for multiple columns, just select one cell in any row.
Next, click on the "Data Entry Rules" (button shown below) in the "Data" tab of the toolbar.
In the "Settings" tab of the menu that appears, select "Apply changes to all cells with the same input rule".without forgettingCheck the box. If you make changes without checking this box, the cell will be excluded from "all cells with the same input rule" and it will be difficult to correct it later.
Next, in the "Error Messages" tab, make sure "Show error message if invalid data is entered" is checked.
Then change the "Style" item to "Information" and click the "OK" button.
The "Title" and "Error Message" fields are optional. There is no difference in functionality if you do not enter them.
If you try to enter an item that is not on the list in this state, an error message will appear as shown in the figure below.
Click "Cancel" to return to the previous entry without confirming it. Clicking "OK" will allow you to enter an item that is not on the list.
Conclusion
When you try to enter an item that is not on the list by changing the input rule settings,
- Disable any input
- Enter after the message is sent out.
- Allow you to type without any messages.
You can choose between three methods
I often "disallow any input at all" because it would be a problem if you try to use the VLOOKUP function or the INDEX/MATCH function to make a later tally, etc. without an exact match, but you can use different methods depending on the situation.
- How to use the INDEX/MATCH functionHow to use Excel (Excel) INDEX and MATCH functions in combination.
- To shade a cell when you enter an entry that is not on the listConditional formatting to change the cell color when an item not in the pull-down (drop-down) list is entered Excel (Excel).
How to create a pull-downCreating a pull-down (drop-down) Excel (Excel).
Comment