엑셀] 동적이름으로 항목이 추가되면 자동으로 리스트 갱신하기

아래 그림은 데이터 유효성 검사로 부서이름을 목록으로 지정해서 입력셀의 세모 아이콘을 클릭하면 입력할 수 있는 목록이 리스트로 나타나도록 설정해 놓은 상태이다. 

이런 데이터 유효성 검사 기능은 입력할 때의 오류를 미리 막아주고, 데이터 입력 효율을 높일 수 있어서 편리하다. 

 - 엑셀] 입력할 데이터를 미리 정하기 - 유효성 검사

단, 일단 이렇게 설정한 다음 부서명이 추가될 경우 데이터 유효성에서 설정한 목록을 다시 지정해 주어야 한다. 

"동적 이름 정의"를 사용해서 목록이 추가되면 자동으로 반영되도록 수정해보자. 

[수식 탭 - 이름 정의]를 클릭하고 수식 부분에 다음과 같이 적자. 

=OFFSET(동적이름!$G$1, 0, 0, COUNTA(동적이름!$G:$G))

(현재 작업 워크시트의 이름 -> "동적이름")

참조대상을 입력할 때 키보드 화살표를 움직일 때마다 셀 주소가 바뀐다면 F2를 눌러서 포인트모드에서 입력모드로 변경한다. 

 - 엑셀] 수식 입력창에서 화살표 키보드 이용하기

위 수식은 OFFSET함수를 사용한다. 

OFFSET함수의 기본 사용방법은

OFFSET(기준셀, 행방향 이동 수, 열방향 이동 수, 참조 높이, 참조 너비)를 나타낸다. 

즉 해당 수식은 해당시트(동적이름)의 G1셀로부터 행방향 이동수 0, 열방향 이동수 0, 

그리고 참조 높이는 counta로 G열의 개수를 count해서 반영하도록 되어 있는 수식이다. 

이제 소속부서명을 데이터 유효성 검사로 설정해보자 

제한대상은 목록으로, 원본은 소속명으로 설정한다. 

(데이터 유효성 검사 설정에 관한 내용은 본 블로그 제일 위 이전 블로그 글  링크 참고)

이제 부서명이 추가되면 자동으로 데이터 유효성 검사 항목에도 자동으로 반영된다.