엑셀 드롭다운 목록 다른 시트에서 안 될 때 — 이름 정의로 해결하기

 

엑셀에서 데이터 유효성 검사로 드롭다운 목록을 만들 때, 다른 시트의 범위를 직접 입력하면 오류가 납니다. "원본은 현재 워크시트의 구분된 목록 또는 참조여야 합니다"라는 메시지가 뜨는 바로 그 상황입니다. 해결법은 이름 정의(Name Manager)를 쓰는 것입니다. 5분이면 됩니다.

이 글에서 답하는 질문

• 드롭다운을 다른 시트 데이터로 만들면 왜 오류가 나나?
• 이름 정의는 어떻게 쓰나?
• 목록이 바뀔 때 자동으로 업데이트되게 하려면?
• 드롭다운 목록을 다른 사람이 못 수정하게 하려면?
• INDIRECT 함수로 동적 드롭다운을 만들려면?

이름 상자에서 이름 정의하는 화면

---

왜 다른 시트를 직접 참조하면 오류가 나나

데이터 유효성 검사의 목록 원본에 =Sheet2!$A$1:$A$10 처럼 다른 시트 주소를 직접 입력하면:

❌ "원본은 현재 워크시트의 구분된 목록 또는 참조여야 합니다."

이 오류가 납니다. 엑셀의 데이터 유효성 검사는 보안 정책상 외부 시트를 직접 참조할 수 없습니다. 이 제한을 우회하는 방법이 이름 정의입니다.

---

해결법 — 이름 정의 사용

1단계. 목록이 있는 시트로 이동

예: Sheet2의 A1:A10에 드롭다운 목록으로 쓸 항목이 있는 경우

2단계. 범위 선택 후 이름 정의

방법 A — 이름 상자 사용 (빠른 방법)

1. Sheet2에서 A1:A10 선택
2. 왼쪽 위 이름 상자 (셀 주소 표시되는 곳)를 클릭
3. 원하는 이름 입력 (예: 부서목록) → Enter

방법 B — 이름 관리자 사용 (관리하기 좋은 방법)

1. 수식 탭 → 이름 관리자 → 새로 만들기
2. 이름: 부서목록
3. 참조 대상: =Sheet2!$A$1:$A$10
4. 확인

3단계. 드롭다운을 적용할 셀로 이동

4단계. 데이터 유효성 검사에서 이름 사용

1. 드롭다운을 만들 셀 선택
2. 데이터 탭 → 데이터 유효성 검사
3. 제한 대상: 목록
4. 원본: =부서목록 (= 기호 + 정의한 이름)
5. 확인

이제 다른 시트의 데이터로 드롭다운이 만들어집니다.

---

목록이 늘어나도 자동 반영되게 하기

이름 정의를 고정 범위($A$1:$A$10)로 하면, 목록에 항목을 추가해도 드롭다운에 반영되지 않습니다.

해결법: OFFSET 함수로 동적 범위 정의

이름 관리자에서 참조 대상을 이렇게 입력합니다:

=OFFSET(Sheet2!$A$1, 0, 0, COUNTA(Sheet2!$A:$A), 1)

• OFFSET(시작셀, 행이동, 열이동, 높이, 너비)
• COUNTA(Sheet2!$A:$A): A열에서 비어 있지 않은 셀 수 → 목록 길이를 자동 계산

이렇게 하면 Sheet2의 A열에 항목을 추가하는 순간 드롭다운에도 자동 반영됩니다.

더 간단한 방법: 표(Table) 활용

Sheet2의 목록을 표(Table)로 만들고, 이름 정의에서 표의 열을 참조합니다.

=부서목록[부서명]

(표 이름이 부서목록, 열 이름이 부서명인 경우)

표는 행을 추가하면 자동으로 범위가 늘어나므로, 가장 관리하기 편한 방법입니다.

---

드롭다운 선택에 따라 목록이 바뀌는 연동 드롭다운

예를 들어 대분류를 "영업팀"으로 선택하면, 소분류 드롭다운에 영업팀 직원 목록이 뜨도록 하는 경우입니다.

INDIRECT 함수 활용

1. 대분류 목록(예: 영업팀, 생산팀, 관리팀)을 이름으로 정의
2. 각 팀원 목록을 팀 이름과 동일한 이름으로 정의
   • 이름: 영업팀 → 참조: =Sheet2!$B$1:$B$5
   • 이름: 생산팀 → 참조: =Sheet2!$C$1:$C$5
3. 소분류 드롭다운 원본: =INDIRECT(대분류셀주소)
   • 예: =INDIRECT(B2) (B2에 대분류 선택값이 있는 경우)

이렇게 하면 B2에서 "영업팀"을 고르면 소분류에 영업팀 직원이, "생산팀"을 고르면 생산팀 직원이 나옵니다.

주의: 이름 정의의 이름이 셀 값과 정확히 일치해야 합니다. 공백이나 오타가 있으면 INDIRECT가 빈 결과를 반환합니다.

---

실무에서 자주 쓰는 케이스

상황	방법
단순 목록, 변경 없음	이름 정의 (고정 범위)
목록이 자주 추가됨	표(Table) 기반 이름 정의
목록이 자주 추가됨 (표 사용 어려울 때)	OFFSET + COUNTA 동적 범위
드롭다운 연동 (대→소 분류)	INDIRECT 함수
목록을 다른 사람이 수정 못 하게	Sheet2를 시트 보호로 잠금

---

목록 시트 보호 — 다른 사람이 못 수정하게

드롭다운 목록이 있는 Sheet2를 보호합니다.

검토 탭 → 시트 보호 → 암호 설정 (선택) → 확인

이후 Sheet2의 내용은 암호를 입력해야만 수정할 수 있습니다. 드롭다운을 쓰는 다른 시트는 영향받지 않습니다.

---

마무리

다른 시트 드롭다운 오류는 이름 정의 한 번으로 완전히 해결됩니다. 목록이 늘어날 걸 대비해 처음부터 표(Table)로 만들어 두는 것이 가장 편합니다.

연동 드롭다운이 필요하면 INDIRECT 함수를 쓰는데, 이름과 셀 값이 정확히 일치해야 한다는 점만 주의하면 됩니다.

피벗테이블 새로 고침 문제 → [엑셀 피벗테이블 새로고침 안 될 때]
엑셀 VLOOKUP 대사 → [매입세금계산서 누락 찾기 — VLOOKUP 대사]

드롭다운-이름정의-실습파일.xlsx

0.01MB