Excel 의 드롭다운 목록 은 강력한 도구입니다. 선택 시 선택 목록을 제공하는 드롭다운 화살표를 사용자에게 제공할 수 있습니다.
이렇게 하면 사용자가 직접 답변을 입력하지 않아도 되므로 데이터 입력 오류를 줄일 수 있습니다. Excel 을 사용하면 셀 범위에서 해당 드롭다운 목록의 항목을 가져올 수도 있습니다.
그러나 여기서 그치지 않습니다. 드롭다운 셀에 대한 데이터 유효성 검사를 구성하는 몇 가지 창의적인 방법을 사용하여 여러 개의 연결된 드롭다운 목록을 만들 수도 있습니다. 여기서 두 번째 목록에서 사용할 수 있는(available in a second list) 항목 은 첫 번째 목록에서 사용자가 만든 섹션에 따라 다릅니다.
다중 연결(Linked) 드롭다운 목록이 좋은(Good) 이유는 무엇입니까 ?
대부분의 온라인 양식은 이전의 드롭다운 목록에서 답변한 내용을 기반으로 보조 드롭다운 목록을 작성한다는 점을 고려하십시오. 즉 , 온라인 양식만큼 고급 Excel 데이터 입력 시트 를 만들 수 있습니다 . 사용자의 답변에 따라 자체적으로 수정됩니다.
예를 들어 Excel 스프레드시트를 사용하여 (Excel)컴퓨터 수리(computer repairs) 가 필요한 사용자로부터 컴퓨터 정보를 수집 한다고 가정해 보겠습니다 .
입력 옵션은 다음과 같습니다.
- 컴퓨터 부품(Computer Part) : 모니터, 마우스(Mouse) , 키보드(Keyboard) , 베이스 시스템(Base System)
- 부품 유형:
- 모니터(Monitor) : 유리, 하우징(Housing) , 전원 코드(Power Cord) , 내부 전자 제품(Internal Electronics)
- 마우스(Mouse) : 휠, LED 라이트(LED Light) , 코드(Cord) , 버튼(Buttons) , 케이스
- 키보드(Keyboard) : 키, 하우징(Housing) , 멤브레인(Membrane) , 코드(Cord) , 내부 전자장치(Internal Electronics)
- 기본 시스템(Base System) : 케이싱, 버튼(Buttons) , 포트(Ports) , 전원(Power) , 내부 전자 장치(Internal Electronics) , 운영 체제(Operating System)
이 트리에서 볼 수 있듯이 "부품 유형"에 대해 선택할 수 있어야 하는 정보 는 사용자가 첫 번째 드롭다운 목록에서 선택 하는 컴퓨터 부품 에 따라 다릅니다.(Computer Part)
이 예에서 스프레드시트는 다음과 같이 보일 수 있습니다.
연결된 드롭다운 목록을 여러 개 만드는 경우 B1의 드롭다운 목록에서 선택한 항목을 사용하여 B2의 드롭다운 목록 내용을 구동할 수 있습니다.
이를 설정하는 방법을 살펴보겠습니다. 또한 아래 예제와 함께 예제 Excel 시트를 자유롭게 다운로드하십시오.
드롭다운 목록 소스 시트 만들기(List Source Sheet)
이와 같이 설정하는 가장 깔끔한 방법은 모든 드롭다운 목록 항목을 구성할 수 있는 Excel 에서 새 탭을 만드는 것입니다.(Excel)
이러한 연결된 드롭다운 목록을 설정하려면 맨 위에 있는 머리글이 첫 번째 드롭다운 목록에 포함하려는 모든 컴퓨터 부품인 테이블을 만듭니다. 그런 다음 해당 헤더 아래에 있어야 하는 모든 항목(부품 유형)을 나열합니다.
다음으로, 나중에 데이터 유효성 검사(Data Validation) 를 설정할 때 올바른 범위를 선택할 수 있도록 각 범위를 선택하고 이름을 지정하려고 합니다 .
이렇게 하려면 각 열 아래에 있는 모든 항목을 선택하고 선택한 이름은 헤더와 동일한 범위를 선택합니다. 테이블 이름을 지정하려면 "A" 열 위의 필드에 이름을 입력하기만 하면 됩니다.
예를 들어 A2 에서 A5 셀을 선택 하고 해당 범위의 이름을 "모니터"로 지정합니다.
모든 범위의 이름이 적절할 때까지 이 프로세스를 반복합니다.
이를 수행하는 다른 방법은 Excel의 선택(Selection) 항목 에서 만들기(Create) 기능을 사용하는 것입니다. 이렇게 하면 위의 수동 프로세스와 같이 모든 범위의 이름을 지정할 수 있지만 한 번만 클릭하면 됩니다.
이렇게 하려면 생성한 두 번째 시트에서 모든 범위를 선택하면 됩니다. 그런 다음 메뉴 에서 수식(Formulas) 을 선택 하고 리본에서 선택 항목에서 만들기를 선택 합니다.(Create from Selection)
팝업 창이 나타납니다. 맨 위 행만(Top row) 선택되어 있는지 확인한 다음 확인 을 선택 합니다(OK) .
이렇게 하면 맨 위 행의 헤더 값을 사용하여 그 아래에 있는 각 범위의 이름을 지정합니다.
첫 번째 드롭다운 목록 설정
이제 여러 개의 연결된 드롭다운 목록을 설정할 차례입니다. 이것을하기 위해:
1. 첫 번째 시트로 돌아가서 첫 번째 레이블 오른쪽에 있는 빈 셀을 선택합니다. 그런 다음 메뉴에서 데이터(Data) 를 선택 하고 리본에서 데이터 유효성 검사(Data Validation) 를 선택 합니다.
2. 열리는 데이터 유효성 검사 창의 (Data Validation)허용(Allow) 아래 에서 목록 을 선택하고 (List)소스(Source) 아래 에서 위쪽 화살표 아이콘을 선택합니다. 이렇게 하면 이 드롭다운 목록의 소스로 사용할 셀 범위를 선택할 수 있습니다.
3. 드롭다운 목록 소스 데이터를 설정한 두 번째 시트를 선택한 다음 헤더 필드만 선택합니다. 선택한 셀의 초기 드롭다운 목록을 채우는 데 사용됩니다.
4. 선택 창에서 아래쪽 화살표를 선택하여 데이터 유효성 검사(Data Validation) 창을 확장합니다. 선택한 범위가 이제 소스(Source) 필드에 표시되는 것을 볼 수 있습니다. 확인(OK) 을 선택 하여 완료합니다.
5. 이제 기본 시트로 돌아가서 첫 번째 드롭다운 목록에 두 번째 시트의 각 헤더 필드가 포함되어 있음을 알 수 있습니다.
이제 첫 번째 드롭다운 목록이 완료되었으므로 다음으로 연결된 드롭다운 목록을 만들 차례입니다.
첫 번째 드롭다운 목록 설정
첫 번째 셀에서 선택한 항목에 따라 목록 항목을 로드할 두 번째 셀을 선택합니다.
위의 과정을 반복하여 데이터 유효성 검사(Data Validation) 창을 엽니다. 허용(Allow) 드롭다운 에서 목록(List) 을 선택 합니다. 소스(Source) 필드는 첫 번째 드롭다운 목록에서 선택한 항목에 따라 목록 항목을 가져오는 필드입니다.
이렇게 하려면 다음 공식을 입력하십시오.
=INDIRECT($B$1)
INDIRECT 기능 은 어떻게 작동합니까?
이 함수는 텍스트 문자열에서 유효한 Excel 참조(이 경우 범위)를 반환합니다. 이 경우 텍스트 문자열은 첫 번째 셀($B$1)이 전달한 범위의 이름입니다. 따라서 INDIRECT는 범위 이름을 가져온 다음 해당 이름과 연결된 올바른 범위로 드롭다운 데이터 유효성 검사를 제공합니다.
참고(Note) : 첫 번째 드롭다운에서 값을 선택하지 않고 이 두 번째 드롭다운에 대한 데이터 유효성 검사를 구성하면 오류 메시지가 표시됩니다. 예(Yes) 를 선택 하여 오류를 무시하고 계속할 수 있습니다.
이제 새로운 여러 개의 연결된 드롭다운 목록을 테스트하십시오. 첫 번째 드롭다운을 사용하여 컴퓨터 부품 중 하나를 선택합니다. 두 번째 드롭다운을 선택하면 해당 컴퓨터 부품에 대한 적절한 목록 항목이 표시되어야 합니다. 이는 해당 부품 에 대해 입력한 두 번째 시트(on the second sheet) 의 열에 있는 부품 유형입니다 .
Excel 에서 (Excel)여러 연결(Multiple Linked) 드롭다운 목록 사용
보시다시피, 이것은 스프레드시트를 훨씬 더 동적으로 만드는 매우 멋진 방법입니다. 사용자 가 다른 셀에서 선택한(select in other cells) 항목에 대한 응답으로 후속 드롭다운 목록을 채우면 스프레드시트가 사용자에게 훨씬 더 민감하게 반응하고 데이터가 훨씬 더 유용해집니다.
위의 팁을 활용하여 스프레드시트에서 어떤 종류의 흥미로운 연결된 드롭다운 목록을 만들 수 있는지 확인하세요. 아래 댓글 섹션에서 흥미로운 팁을 공유 하세요.(Share)
How to Create Multiple Linked Drop Down Lists in Excel
Drop-down lists in Excel are powerful tools. Τhey let you provide users with a drop-down arrow that, when selected, provides them with a list of choices.
This can reduce data-entry errors because it avoids users from having to type answers directly. Excel even lets you pull the items for those drop-down lists from a range of cells.
However, it doesn’t stop there. Using some creative ways to configure data validation for drop-down cells, you can even create multiple, linked drop-down lists, where the items that are available in a second list depend on the section the user made in the first list.
What Are Multiple Linked Drop-Down Lists Good For?
Consider that most forms online fill out secondary drop-down lists based on what you answer in the drop-down list before it. This means you can make your Excel data-entry sheets just as advanced as online forms. It will modify itself based on the user’s answers.
For example, let’s say you’re using an Excel spreadsheet to collect computer information from your users who need computer repairs.
The entry options might look like this:
- Computer Part: Monitor, Mouse, Keyboard, Base System
- Part Type:
- Monitor: Glass, Housing, Power Cord, Internal Electronics
- Mouse: Wheel, LED Light, Cord, Buttons, Casing
- Keyboard: Keys, Housing, Membrane, Cord, Internal Electronics
- Base System: Casing, Buttons, Ports, Power, Internal Electronics, Operating System
As you can see from this tree, the information that should be available to select for “Part Type” depends on which Computer Part the user selects in the first dropdown list.
In this example, your spreadsheet may start out looking something like this:
If you create multiple, linked drop-down lists, you can use the item selected from the drop-down list in B1 to drive the contents of the drop-down list in B2.
Let’s take a look at how you can set this up. Also, feel free to download our example Excel sheet with the example below.
Create Your Drop-Down List Source Sheet
The cleanest way to set something like this up is to create a new tab in Excel where you can configure all of your drop-down list items.
To set up these linked drop-down lists, create a table where the header at the top are all of the computer parts you want to include in the first dropdown list. Then list all of the items (part types) that should go under that header.
Next, you’ll want to select and name each range so that when you’re setting up Data Validation later, you’ll be able to select the correct one.
To do this, select all of the items under each column and name that selected range the same as the header. To name a table, you simply type the name in the field over the “A” column.
For example, select cells A2 through A5, and name that range “Monitor”.
Repeat this process until you have all of the ranges named appropriately.
An alternative way to do this is using Excel’s Create from Selection feature. This lets you name all of the ranges like the manual process above, but with a single click.
To do this, just select all of the ranges in the second sheet that you created. Then select Formulas from the menu, and select Create from Selection in the ribbon.
A pop-up window will appear. Make sure only Top row is selected and then select OK.
This will use the header values in the top row to name each of the ranges underneath it.
Set Up Your First Drop Down List
Now it’s time to set up your multiple, linked drop-down lists. To do this:
1. Back on the first sheet, select the blank cell to the right of the first label. Then select Data from the menu, and select Data Validation in the ribbon.
2. In the Data Validation window that opens, select List under Allow, and under Source, select the up arrow icon. This will let you select the range of cells you want to use as the source for this drop-down list.
3. Select the second sheet where you set up the drop-down list source data, and then select only the header fields. These will be used to fill out the initial drop-down list in the cell you’ve selected.
4. Select the down arrow in the selection window to expand the Data Validation window. You’ll see the range you’ve selected now displayed in the Source field. Select OK to finish.
5. Now, back on the main sheet, you’ll notice that the first drop-down list contains each of the header fields from the second sheet.
Now that your first drop-down list is finished, it’s time to create your next, linked drop-down list.
Set Up Your First Drop Down List
Select the second cell that you want to load list items for depending on what’s selected in the first cell.
Repeat the process above to open the Data Validation window. Select List in the Allow drop-down. The Source field is what will pull in list items depending on what’s selected in the first drop-down list.
To do this, enter the following formula:
=INDIRECT($B$1)
How does the INDIRECT function work?
This function returns a valid Excel reference (in this case to a range), from a text string. In this case, the text string is the name of the range passed by the first cell ($B$1). So INDIRECT takes the name of the range and then provides the drop-down data validation with the correct range associated with that name.
Note: If you configure the data validation for this second drop-down without selecting a value from the first drop-down, you’ll see an error message. You can select Yes to ignore the error and continue.
Now, test your new multiple, linked drop-down lists. Use the first drop-down to select one of the computer parts. When you select the second drop-down, you should see the appropriate list items for that computer part. These were the part types in the column on the second sheet that you filled in for that part.
Using Multiple Linked Drop-Down Lists in Excel
As you can see, this is a very cool way to make your spreadsheets much more dynamic. By filling in subsequent drop-down lists in response to what users select in other cells, you can make your spreadsheets far more responsive to users, and the data far more useful.
Play around with the tips above and see what kind of interesting linked drop-down lists you can create in your spreadsheets. Share some of your own interesting tips in the comments section below.
