Bài viết: Chiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự động

[TranThanhPhong]

Chiêu 38: Thêm dữ liệu vào danh sách Validation một cách tự động



Nếu bạn đã từng sử dụng validation, bạn sẽ thấy đó là một tính năng rất hay. Có lẽ điểm ấn tượng nhất của nó chính là khả năng thêm một danh sách lựa chọn vào bất kỳ ô nào trên bảng tính và cho phép người sử dụng chọn lựa. Nó sẽ tuyệt hơn hay không nếu khi bạn nhập vào một tên mới trong một ô đang áp dụng tính năng validation thì Excel sẽ tự động thêm tên này vào trong danh sách validation? Điều này có thể thực hiện được khi bạn làm theo các hướng dẫn trong bài này.

Giả sử bạn có danh sách tên trong vùng A1:A10 như hình sau:

Danh sách này chính là tên của các nhân viên trong một công ty. Tại một ô đang áp dụng validation, bạn sẽ không thể nào nhập vào được tên một nhân viên mới ngoài danh sách lựa chọn, mà bạn phải thêm tên nhân viên mới này vào dòng cuối trong danh sách và điều chỉnh lại vùng dữ liệu của validation trước. Điều này sẽ rất bất tiện trong sử dụng.

Để hạn chế nhược điểm này, tại ô A11 bạn nhập vào công thức bên dưới và sao chép đến A20 (dự trù trước sẽ thêm 10 tên mới).

=IF(OR($D$1="",COUNTIF($A$1:A10,$D$1)),"x",$D$1)

Chọn Formulas ➝ Defined Names ➝ Define Name (E2003: Insert ➝ Name ➝ Define), và nhập vào tên MyName tại hộp Names. Tại Refers To, bạn nhập vào công thức bên dưới rồi nhấp OK (E2003: nhấn Add ➝ OK).

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

Theo tôi, ta thay bằng công thức
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-COUNTIF(Sheet1!$A:$A,"=x"),1)

Choạ ô D1, vào Data ➝ Data Tools ➝ Data Validation (E2003: Data ➝ Validation). Chọn List từ hộp Allow, và tại Source nhập vào =MyNames, bạn đảm bảo rằng đã chọn thêm hộp In-Cell dropdown. Chọn ngăn Error Alert và bỏ chọn Show error alert after invalid data is entered. Nhấn nút OK khi hoàn tất.

Nhấp phải chuột lên tên Sheet1 và chọn View Code. Sau đó bạn nhập vào đoạn mã sau:

 Private Sub Worksheet_Change(ByVal Target As Range)
      Dim lReply As Long
      If Target.Cells.Count > 1 Then Exit Sub
      If Target.Address = "$D$1" Then
          If IsEmpty(Target) Then Exit Sub
          If WorksheetFunction.CountIf(Range("MyNames"), Target) = 0 Then
              lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
              If lReply = vbYes Then
                  Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target
              End If
          End If
      End If
  End Sub

Đóng cửa sổ VBE và lưa bảng tính lại, sau đó chọn ô D1 và nhập vào một tên mới chưa có trong danh sách validation và nhấn Enter. Bạn sẽ thấy tên mới xuất xuất hiện trong danh sách dữ liệu trên bảng tính tại ô A11 và nếu chọn ô D1 và mở danh sách validation bạn cũng sẽ thấy tên mới đã được bổ sung vào.

Các bạn xem thêm một số cải tiến trong tập tin đính kèm.

MỤC LỤC Những tuyệt chiêu trong Excel
​

Một số bài viết có liên quan:
1/ Chiêu 37: Chuyển đổi các hàm và công thức trong Excel thành giá trị
2/ Chiêu thứ 36: Làm nổi các Subtotal của Excel
3/ Chiêu 35: Tự tạo danh sách để fill
4/ Chiêu 33: Tạo các định dạng số cho riêng bạn
5/ Chiêu 32: Thao tác trên dữ liệu với Advanced Filter
6/ Chiêu thứ 31: Sắp xếp ngẫu nhiên
7/ Chiêu thứ 29: Tùy biến chú thích của ô bảng tính
8/ Chiêu 28: Trích xuất dữ liệu số trong 1 chuỗi bằng VBA
9/ Chiêu thứ 27: Chuyển đổi con số dạng văn bản sang số thực
10/ Chiêu 26: Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn
http://www.giaiphapexcel.com/vbb/content.php?357

 

[ntxuyen]

Xin ad chỉ giúp nếu ô D1 nằm ở sheet2 (Mylist vẫn ở sheet1) thì chỉnh code lại ntn @TranThanhPhong?