엑셀_종속드롭다운_연동

엑셀에서 "상의"를 고르면 옆 칸에 반팔티·셔츠·니트만, "하의"를 고르면 청바지·슬랙스만 나오게 하는 종속(연동) 드롭다운은 이름 정의 + =INDIRECT($B5) 두 단계면 만들어집니다. 5분이면 끝나는데, 이름에 공백이 있거나 $를 잘못 붙이면 목록이 안 뜹니다. 첨부한 실습 파일(의류 품목 3개 대분류, 15개 소분류)로 만드는 법과 안 될 때 원인을 같이 정리합니다.

완성 모습

대분류 (B열)	소분류 (C열)에 뜨는 목록
상의	반팔티, 긴팔티, 셔츠, 니트, 맨투맨
하의	청바지, 슬랙스, 조거팬츠, 반바지
아우터	자켓, 코트, 패딩, 가디건, 점퍼, 베스트

B열에서 고른 값에 따라 C열 목록이 바뀝니다. 품목 분류, 부서→담당자, 시·도→시·군·구처럼 2단계로 고르는 입력표에 다 쓰입니다.

1단계. 원본 목록을 세로로 정리

별도 시트(목록)에 이렇게 만듭니다.

A열	B열	C열
상의	하의	아우터
반팔티	청바지	자켓
긴팔티	슬랙스	코트
셔츠	조거팬츠	패딩
니트	반바지	가디건
맨투맨		점퍼
		베스트

맨 윗줄 = 대분류 이름, 그 아래 = 소분류입니다. 이 구조가 다음 단계에서 그대로 이름이 됩니다.

2단계. 이름 정의 — "선택 영역에서 만들기"로 한 번에

하나씩 만들 필요 없습니다.

1. 목록 시트에서 A4:C10 (머리글 포함 전체) 선택
2. 수식 → 선택 영역에서 만들기 (단축키 Ctrl+Shift+F3)
3. 첫 행만 체크 → 확인

이러면 상의, 하의, 아우터라는 이름이 각 열의 소분류 범위에 붙습니다. 수식 → 이름 관리자에서 확인해 보세요.

빈 칸이 범위에 들어가는 문제: 열마다 항목 수가 다르면 빈 칸까지 이름에 포함돼서 드롭다운 끝에 공백이 생깁니다. 거슬린다면 이름 관리자에서 각 이름의 참조 범위를 마지막 항목까지만 줄여 주세요. 첨부 파일은 이렇게 맞춰 두었습니다.

대분류 목록에도 이름을 하나 붙여 둡니다.

이름: 대분류   참조 대상: =목록!$A$4:$C$4

이름 관리자

3단계. 대분류 드롭다운 (B열)

1. 입력 시트 B5:B19 선택
2. 데이터 → 데이터 유효성 검사
3. 제한 대상: 목록
4. 원본:

=대분류

4단계. 소분류 드롭다운 (C열) — 핵심

1. C5:C19 선택 (반드시 C5부터 선택)
2. 데이터 → 데이터 유효성 검사 → 목록
3. 원본:

=INDIRECT($B5)

INDIRECT는 B5에 적힌 글자 "상의"를 이름 상의로 바꿔서 그 범위를 가져옵니다. 이게 연동의 원리 전부입니다.

"원본이 현재 오류 상태입니다"라는 경고가 뜨면 예를 누르세요. B5가 아직 비어 있어서 생기는 경고라 정상입니다.

데이터 유효성

안 될 때 원인 5가지

증상	원인	해결
모든 행에서 같은 목록만 뜸	$B$5로 행까지 고정함	$B5로 (열만 고정)
특정 대분류만 목록이 안 뜸	대분류 이름에 공백·하이픈 ("아우터 상의", "T-shirt")	아래 SUBSTITUTE 방법
숫자로 시작하는 대분류만 안 됨	이름은 숫자로 시작할 수 없음 ("1공장")	"공장1"처럼 바꾸거나 앞에 _ 붙이기
"A1", "BC2" 같은 대분류가 안 됨	셀 주소와 같은 이름은 정의 불가	이름 앞에 문자 추가
다른 파일의 목록을 쓰는데 가끔 안 뜸	INDIRECT는 닫힌 파일을 못 읽음	목록 시트를 같은 파일에 넣기

대분류에 공백이 꼭 필요할 때

이름에는 공백을 쓸 수 없어서 엑셀이 자동으로 아우터_상의처럼 밑줄로 바꿉니다. 그런데 B열에는 "아우터 상의"(공백)가 입력되니 이름을 못 찾습니다. 원본 수식에서 공백을 밑줄로 바꿔 주면 됩니다.

=INDIRECT(SUBSTITUTE($B5," ","_"))

하이픈이 있다면 SUBSTITUTE를 한 번 더 감싸면 됩니다.

함정 하나 — 대분류를 바꿔도 소분류는 그대로 남는다

종속 드롭다운의 가장 큰 약점입니다. 상의-니트로 입력한 뒤 B열을 하의로 바꿔도 C열의 "니트"는 지워지지 않습니다. 결과적으로 "하의-니트" 같은 잘못된 데이터가 남습니다.

실습 파일 4번째 행(상의-청바지)이 바로 그 상황입니다. 그래서 확인 열을 하나 둡니다.

=IF(C5="","",IF(COUNTIF(INDIRECT(B5),C5)>0,"OK","대분류와 불일치"))

소분류가 해당 대분류 목록 안에 있는지 COUNTIF로 세는 수식입니다. 없으면 "대분류와 불일치"가 뜨고 조건부 서식으로 빨갛게 칠해집니다. 실습 파일 오른쪽 점검 칸에 불일치 건수(1건)가 나옵니다.

발주서나 재고표처럼 여러 사람이 입력하는 파일이라면 이 확인 열은 꼭 넣으세요. 피벗으로 집계할 때 "하의-니트" 한 줄이 숫자를 틀어지게 만듭니다.

목록을 다른 시트에 둘 때

예전 엑셀(2007 이하)에서는 데이터 유효성 검사 원본에 다른 시트를 직접 참조할 수 없어서 이름 정의가 필수였습니다. 지금도 이름 정의를 쓰는 편이 관리하기 쉽습니다. 다른 시트 목록이 안 뜨는 경우는 엑셀 드롭다운 목록 다른 시트에서 안 될 때 — 이름 정의로 해결하기에 정리해 두었습니다.

소분류 항목을 추가할 때

니트 아래에 "후드티"를 추가했는데 드롭다운에 안 나온다면 이름 범위가 고정돼 있기 때문입니다.

• 수식 → 이름 관리자 → 상의 선택 → 참조 대상 범위를 한 칸 늘리기
• 항목이 자주 늘어난다면 처음부터 범위를 넉넉하게(예: 20행) 잡고, 빈 칸이 드롭다운에 보이는 건 감수하는 방법도 있습니다

마무리

• 이름 정의(Ctrl+Shift+F3, 첫 행) → 원본 =INDIRECT($B5) 두 단계면 됩니다.
• $B5처럼 열만 고정하세요. $B$5면 모든 행이 같은 목록이 됩니다.
• 대분류를 바꿔도 소분류는 안 지워집니다. COUNTIF 확인 열로 불일치를 잡으세요.

첨부한 실습 파일에서 B열 값을 바꿔 가며 C열 목록이 바뀌는지, 오른쪽 불일치 건수가 1에서 0으로 줄어드는지 확인해 보세요.

관련 글

• 엑셀 드롭다운 목록 다른 시트에서 안 될 때 — 이름 정의로 해결하기
• 엑셀 피벗테이블 새로고침 안 될 때 — 원인 5가지와 해결법
• VLOOKUP은 이제 그만 — XLOOKUP 함수 사용방법

참고

• Microsoft 지원 — INDIRECT 함수: https://support.microsoft.com/office/474b3a3a-8a26-4f44-b491-92b6306fa261

메뉴 위치는 Microsoft 365와 Excel 2016 이후 기준입니다.