엑셀에서 데이터 유효성 검사(Data Validation)를 적용하면 잘못된 데이터 입력을 사전에 차단할 수 있습니다. 이 글에서는 다른 시트의 데이터를 이용해 데이터 유효성 검사 목록을 만드는 6가지 방법을 단계별로 소개합니다. 예제 데이터셋에는 제품(Product)과 카테고리(Category) 열이 있으며, 진행 과정에 따라 데이터셋이 조금씩 변화합니다.
다른 시트 데이터로 유효성 검사 목록 활용하기: 6가지 방법
1. 다른 시트의 목록으로 드롭다운 리스트 만들기
데이터셋에는 두 개의 열이 있습니다. 고객이 구매한 제품명은 다른 시트의 데이터 유효성 검사 목록을 통해 입력하겠습니다. 단계별로 살펴보겠습니다.
실행 단계:
- 먼저 'dropdown' 시트에서 셀 범위 B5:B11을 선택합니다.
- 리본 메뉴에서 데이터 탭 >>> 데이터 유효성 검사를 클릭합니다.
그러면 데이터 유효성 검사 대화 상자가 나타납니다.
- 제한 대상 드롭다운 메뉴에서 목록(List)을 선택합니다.
참고: 빈 셀 무시와 인셀 드롭다운(In-cell dropdown) 옵션은 기본적으로 체크되어 있습니다. 체크가 해제되어 있다면 반드시 선택해 주세요.
- 원본(Source) 입력 상자를 클릭합니다.
- 'source' 시트로 전환하여 셀 범위 B5:B11을 선택합니다. 이것이 유효성 검사 목록입니다.
- 마지막으로 확인을 누릅니다.
선택한 셀 옆에 화살표 아이콘이 표시됩니다.
- 화살표 아이콘을 클릭하면 다른 시트에 있는 데이터 유효성 검사 목록이 나타납니다.
- 목록에서 원하는 항목을 선택하면 해당 값이 자동으로 입력됩니다.
이렇게 하면 엑셀에서 다른 시트의 목록을 활용해 데이터셋을 손쉽게 작성할 수 있습니다.
2. 다른 시트 데이터로 날짜 범위 제한하기
이번에는 제품의 재고 날짜 열이 비어 있습니다. 이 열을 데이터 유효성 검사 목록으로 채워 보겠습니다. 이번에는 날짜 간(between) 옵션을 사용합니다.
실행 단계:
- 먼저 셀 범위 C5:C11을 선택합니다.
- 데이터 유효성 검사 대화 상자를 엽니다.
- 아래 설정을 지정합니다:
- 제한 대상: 날짜(Date)
- 데이터: between(다음 사이)
- 'source' 시트에서 시작일과 종료일 셀 참조를 지정합니다.
- 시작 날짜: 'source' 시트의 B14 셀
- 종료 날짜: 'source' 시트의 B15 셀
- 마지막으로 확인을 누릅니다.
참조 셀이 이동하지 않도록 절대 참조($ 기호)를 사용하는 것이 안전합니다.
이제 정의된 조건 외의 값을 입력하면 오류 메시지가 표시됩니다. 이 메시지는 원하는 대로 수정할 수 있습니다.
메시지를 변경하려면 데이터 유효성 검사 대화 상자의 오류 경고(Error Alert) 탭으로 이동하세요.
- 제목:에 문구를 입력합니다(선택 사항).
- 오류 메시지:에 원하는 안내 문구를 작성합니다.
- 확인을 눌러 완료합니다.
나머지 필드에는 날짜를 직접 입력할 수 있으며, 설정한 범위를 벗어난 값을 입력하면 경고 메시지가 표시됩니다. 이렇게 유효성 검사 목록으로 셀 입력을 제한할 수 있습니다.
3. 다른 시트 데이터로 시간 범위 제한하기
이번 예제에서는 재고 시간 열에 특정 시간대만 입력되도록 제한하겠습니다. 마찬가지로 기준 값은 다른 시트에 위치합니다.
실행 단계:
- 먼저 셀 범위 D5:D11을 선택합니다.
- 데이터 유효성 검사 대화 상자를 엽니다.
- 다음 옵션을 지정합니다:
- 제한 대상: 시간(Time)
- 데이터: between(다음 사이)
- 시작 시간: 'source' 시트의 F14 셀
- 종료 시간: 'source' 시트의 F15 셀
- 확인을 누릅니다.
이제 재고 시간 열에는 오전 8시부터 오후 5시 사이의 시간만 입력할 수 있습니다.
4. 다른 시트 데이터로 최솟값 초과 조건 적용하기
이번 예제에서는 데이터셋에 새로운 열을 추가하고, 정수(Whole number) 유형에 보다 큼(greater than) 조건을 적용해 유효성 검사를 구성합니다.
실행 단계:
- 먼저 셀 범위 E5:E11을 선택합니다.
- 데이터 탭에서 데이터 유효성 검사 대화 상자를 엽니다.
기존 데이터가 있는 경우 경고 메시지가 표시될 수 있습니다. 이때 아니요(NO)를 클릭하세요.
- 대화 상자에서 다음 항목을 지정합니다:
- 제한 대상: 정수(Whole number)
- 데이터: greater than(다음 값보다 큼)
- 최소값: 'source' 시트의 F7 셀
- 확인을 누릅니다.
이제 판매량 열에는 0보다 큰 값만 입력할 수 있어 잘못된 값 입력을 방지할 수 있습니다.
5. 다른 시트 데이터로 텍스트 길이 제한하기
이번에는 새로운 데이터셋을 준비했습니다. 판매 담당자(Salesperson) 이름을 입력하는데, 짧은 이름만 허용하려고 합니다. 따라서 셀에 입력 가능한 문자 수를 제한해야 합니다.
실행 단계:
- 먼저 셀 범위 C5:C11을 선택합니다.
- 데이터 탭에서 데이터 유효성 검사 대화 상자를 엽니다.
- 다음 옵션을 지정합니다:
- 제한 대상: 텍스트 길이(Text length)
- 데이터: between(다음 사이)
- 최소값: 'source' 시트의 B40 셀
- 최대값: 'source' 시트의 B41 셀
- 확인을 누릅니다.
이렇게 설정하면 정해진 길이 범위 내의 텍스트만 입력되도록 나머지 셀까지 손쉽게 작성할 수 있습니다.
6. 다른 시트 데이터로 종속 드롭다운 목록 만들기
마지막으로 INDIRECT 함수와 이름 정의된 범위(Named Range)를 활용해 종속 드롭다운 메뉴를 만들어 보겠습니다.
실행 단계:
먼저 이름 정의된 범위를 생성합니다.
- 셀 범위 B29:C33을 선택합니다.
- 수식 탭 >>> 선택 영역에서 만들기(Create from Selection)를 클릭합니다.
대화 상자가 나타나면 첫 행(Top row)을 선택하고 확인을 누릅니다.
왼쪽 상단의 이름 상자(Name Box)를 클릭하면 Beverages와 Snacks 두 개의 이름 범위가 생성된 것을 확인할 수 있습니다.
- 이제 셀 B4를 선택하고 데이터 탭에서 데이터 유효성 검사 대화 상자를 엽니다.
- 다음 설정을 지정합니다:
- 제한 대상: 목록(List)
- 원본: 셀 범위 B29:C29 선택
- 확인을 누릅니다.
B5 셀에서 두 개의 카테고리가 드롭다운으로 표시됩니다. 다음 단계는 C5 셀에 종속 목록을 만드는 것입니다.
- 셀 범위 C5:C11을 선택합니다.
- 데이터 탭에서 데이터 유효성 검사 대화 상자를 엽니다.
- 다음 값을 설정합니다:
- 제한 대상: 목록(List)
- 원본: 아래 수식 입력
=INDIRECT(B5)
- 확인을 누릅니다.
C5 셀에서 Snacks 카테고리에 해당하는 제품들이 표시됩니다. B5 셀을 Beverages로 변경하면 C5 셀의 목록도 자동으로 바뀝니다.
같은 방식으로 나머지 셀에도 종속 값을 적용해 완성할 수 있습니다.
연습 섹션
예제 파일의 각 섹션마다 연습용 데이터셋을 제공합니다. 직접 실습하면서 이 6가지 기술을 완전히 익혀 보세요.
결론
이 글에서는 다른 시트의 데이터를 활용해 엑셀에서 유효성 검사 목록을 만드는 6가지 예제를 살펴보았습니다. 드롭다운 리스트, 날짜·시간·숫자·텍스트 길이 제한, 그리고 INDIRECT 함수를 이용한 종속 목록까지 다양하게 응용할 수 있습니다. 궁금한 점이 있다면 댓글로 남겨주세요. 읽어주셔서 감사합니다!
함께 읽으면 좋은 글
- 엑셀 데이터 유효성 검사 수식에 IF문 활용하기(6가지 방법)
- 엑셀 VBA로 배열에서 데이터 유효성 검사 목록 만들기
- 엑셀 데이터 유효성 검사에서 VLOOKUP 사용자 지정 수식 활용법
- [해결] 엑셀에서 복사-붙여넣기 시 데이터 유효성 검사가 작동하지 않을 때
- 엑셀 VBA로 데이터 유효성 검사 목록에 기본값 설정하기(매크로 및 사용자 폼)