부서 고르면 담당자 목록이 알아서 바뀌는 양식, 어떻게 만들까

지출결의서 양식을 만들 때, 부서를 먼저 고르면 그 부서에 속한 담당자만 다음 드롭다운에 나타나게 하고 싶었습니다. 전체 담당자를 한 목록에 다 넣어두면 부서와 상관없는 사람까지 골라야 해서 실수가 잦았거든요. 이전 글에서 다룬 유효성 검사의 목록 기능에, 이름 정의를 조합하면 이런 단계별 드롭다운을 만들 수 있습니다. 오늘은 이 종속 드롭다운을 정리해보겠습니다.

종속 드롭다운이란

첫 번째 드롭다운에서 선택한 값에 따라, 두 번째 드롭다운의 선택지 자체가 바뀌는 방식입니다. 부서를 먼저 고르면 그 부서 담당자만 보이고, 다른 부서를 고르면 목록이 자동으로 바뀝니다. 잘못된 조합(영업팀을 골랐는데 개발팀 담당자를 선택하는 것 같은 상황)이 원천적으로 발생하지 않게 막아줍니다.

준비 단계: 부서별 담당자 목록을 나눠서 정리한다

먼저 별도의 시트나 영역에 부서별로 담당자 명단을 나눠서 세로로 정리해둡니다.

 

A열: 영업팀     B열: 인사팀     C열: 개발팀
김철수          이영희          박민수
정지훈          최수진          강동원

이렇게 부서마다 세로 열로 나눠서 담당자를 정리해두는 게 시작점입니다.

1단계: 각 부서별 목록에 이름 정의하기

이전 글에서 다룬 이름 정의를 여기서 활용합니다. 영업팀 담당자가 있는 범위(A2:A3)를 선택하고 이름 상자에 "영업팀"이라고 입력합니다. 같은 방식으로 인사팀 범위는 "인사팀", 개발팀 범위는 "개발팀"이라는 이름으로 각각 정의합니다.

여기서 중요한 점은 이름이 부서명과 정확히 일치해야 한다는 것입니다. 뒤에서 만들 종속 드롭다운은, 첫 번째 드롭다운에서 선택한 값(부서명)을 그대로 이름으로 사용해서 두 번째 목록을 찾아오기 때문입니다.

2단계: 첫 번째 드롭다운(부서 선택) 만들기

부서를 선택할 셀에 이전 글에서 다룬 유효성 검사를 겁니다. 제한 대상을 '목록'으로, 원본에는 "영업팀,인사팀,개발팀"을 직접 입력하거나, 부서명이 나열된 범위를 지정합니다.

3단계: 두 번째 드롭다운(담당자 선택)에 INDIRECT 함수 걸기

담당자를 선택할 셀에도 유효성 검사를 걸되, 원본 칸에 이렇게 입력합니다.

 

=INDIRECT($D$2)

여기서 D2는 방금 부서를 선택한 셀입니다. INDIRECT 함수는 셀에 적힌 텍스트를 실제 이름(또는 셀 주소)으로 바꿔서 참조해주는 함수입니다. D2에 "영업팀"이라고 적혀 있다면, INDIRECT($D$2)는 곧 앞서 정의해둔 "영업팀"이라는 이름의 범위를 참조하는 것과 같아집니다.

이렇게 설정해두면, D2에서 부서를 "영업팀"으로 고르는 순간 담당자 드롭다운에는 영업팀 담당자만 나타나고, "개발팀"으로 바꾸면 즉시 개발팀 담당자로 목록이 바뀝니다.

부서명에 공백이나 특수문자가 있으면 주의해야 한다

이전 글에서 이름 정의의 규칙을 다뤘던 것처럼, 이름에는 공백을 쓸 수 없습니다. 만약 부서명이 "영업 1팀"처럼 공백이 들어간다면, 이름 정의를 할 때는 "영업_1팀"처럼 밑줄로 바꿔서 등록해야 하고, INDIRECT 함수도 이 공백을 처리해줄 수 있도록 SUBSTITUTE 함수와 조합해서 써야 합니다.

 

=INDIRECT(SUBSTITUTE($D$2, " ", "_"))

D2의 부서명에 있는 공백을 밑줄로 바꾼 뒤 이름으로 참조하는 방식입니다. 이전 글에서 다룬 텍스트 함수(SUBSTITUTE)가 여기서 실제로 응용되는 예시입니다.

3단계 이상으로 확장하기

부서를 고르고, 그 다음 담당자를 고르고, 그 담당자의 최근 프로젝트까지 고르는 것처럼 3단계 이상으로 확장할 수도 있습니다. 원리는 동일합니다. 각 단계마다 이전 단계에서 선택한 값을 기준으로 INDIRECT를 걸어주면 됩니다. 다만 단계가 늘어날수록 관리해야 할 이름 정의 개수도 늘어나므로, 이름 관리자(이전 글에서 다룬 기능)를 활용해서 전체 목록을 주기적으로 점검하는 게 좋습니다.

자주 하는 실수 정리

• 이름 정의와 부서명 텍스트가 정확히 일치하지 않는 경우: 대소문자나 띄어쓰기가 하나라도 다르면 INDIRECT가 값을 찾지 못해 드롭다운이 비어버립니다.
• 첫 번째 드롭다운을 바꾼 뒤 두 번째 드롭다운에 이미 선택되어 있던 값이 안 지워지는 경우: 부서를 영업팀에서 개발팀으로 바꿔도, 이미 선택돼 있던 담당자 값은 자동으로 지워지지 않습니다. 목록에 없는 값이 남아있을 수 있으니, 부서를 바꿀 때마다 담당자도 다시 선택하는 걸 안내 메시지로 알려주는 게 좋습니다.
• INDIRECT 함수가 걸린 파일을 다른 사람이 열었을 때 보안 경고가 뜨는 경우: INDIRECT는 외부 참조와 함께 쓰이면 보안 경고의 대상이 될 수 있으니, 같은 파일 내부의 이름만 참조한다면 큰 문제는 없지만 낯선 경고가 뜨더라도 당황하지 않으셔도 됩니다.
• 부서별 목록 범위를 정확히 지정하지 않아서 빈 칸까지 드롭다운에 포함되는 경우: 이름 정의할 때 실제 담당자가 있는 범위만 정확히 선택해야, 드롭다운에 빈 항목이 섞여 나오지 않습니다.

마치며

종속 드롭다운은 이전 글들에서 다룬 유효성 검사, 이름 정의, INDIRECT, 텍스트 함수를 한데 모아서 활용하는 응용 사례라고 할 수 있습니다. 처음 설정하는 과정이 조금 번거롭게 느껴질 수 있지만, 한 번 만들어두면 양식을 쓰는 사람이 잘못된 조합을 고를 걱정 없이 훨씬 편하게 입력할 수 있습니다. 부서-담당자, 카테고리-세부항목처럼 단계적으로 좁혀지는 선택지가 필요한 양식을 만드신다면 꼭 활용해보시길 추천드립니다.