Click here to see how to create a basic pulldown.Creating a pull-down (drop-down) Excel (Excel)
If you try to select the name of a large number of employees from a pull-down menu, you will have a large number of choices and it will be very difficult to make a selection. Therefore, if you can narrow down the list to a specific department, it will be much easier to make a selection.
Therefore, we will show you how to link the two pull-downs from major categories to minor categories.
There are two main ways to link the two pull-downs: one pattern has the list of references in a single vertical column, and the other has the major categories in a table on the horizontal axis.
Click here for the formerLinking two input rules without macros (for linking only one set) Excel (Excel)We will introduce the latter method in this issue.
Sponsored Links
For an agent specializing in freelance IT engineers, please contact Engineer Room.
Create a departmental employee list
First, create a sheet called "Master" and create a table there with the name of the department in the major category on the first line and the names of the members in the minor categories on the second and subsequent lines.
Name the range of members in each department.
First, select the range in which the members of the General Affairs Department are entered in this example, click on the area where the cell number is displayed, type "General Affairs Department" and press the "Enter" key.
We can now name the range of members of the General Affairs Department "General Affairs Department".
All following departments will be similarly named by department name.
Create a department entry field on the input form.
Next, we will create a sheet called "Forms" and create input forms on it.
Set input rules in the cells where departments are entered.
In this example, with the "B3" cell selected, click on the "Data" tab, then "Data Entry Rules".
Change the input value type to "list".
You will then be prompted to enter the original value, and click on this area.
Then select the range where the department is entered on the "Master" sheet and press the "Enter" key.
When you return to this screen, click the "OK" button.
After that, copy the "B3" cell and stick it over the entire department input column.
The same pull-down is now set for all department input fields.
Create a member entry field on the input form
Next, with the employee name entry field "C3" selected, click on the "Data" tab and then on "Data Entry Rules".
On the screen that appears, change the input value type to "List" and then to the original value.
=INDIRECT(B3)and click the "OK" button.
This is because the major category cell to be linked to the "C3" cell is "B3". The cell number of the major category cell will vary depending on the Excel file you have created.
Now copy and paste the "C3" cell to the bottom, just as you did with the department.
Now you can link each pull-down list separately in each row.
Advantages of this method
- Capable of handling multi-line linkage
This method is convenient because if there are multiple rows of cells to be linked, you can link them all by simply copying them down.
- Easy to make
In this example, the number of departments in the large category was small, so it is very easy to create a system that works together with this amount.
Disadvantages of this method
- A large amount of large portions are hard to make.
When the number of departments in a large category is large, the process of creating a table and registering the names of a range of cells becomes very time-consuming.
- Maintenance is time-consuming when departments are moved.
When a department is moved, both the source and destination of the table need to be corrected, which increases the risk of errors such as collapsing the table and work omissions.
To avoid these disadvantages, consider another way to link them together.Linking two input rules without macros (for linking only one set) Excel (Excel)
Comment