Sử dụng Data Validation khi danh sách nguồn nằm trong một Sheet khác

Nguồn bài viết: Sử dụng Data Validation khi danh sách nguồn nằm trong một Sheet khác

Data Validation là một tính năng cho phép chúng ta kiểm tra quy tắc xác thực khi nhập dữ liệu trong Excel.

Tính năng này thường được sử dụng trong quá trình nhập dữ liệu, đặc biệt khi cần nhập số theo một quy tắc nhất định. Nhu la:

• Điểm là một số tự nhiên lớn hơn hoặc bằng 0 và nhỏ hơn hoặc bằng 10.
• Số điện thoại có 10 ký tự số.
• Số chứng minh nhân dân có 9 chữ số.
• Số định danh công dân có 12 chữ số…

Khi bạn nhập sai (ví dụ: điểm bạn nhập là 11), một hộp thoại thông báo sẽ xuất hiện ngay lập tức, sau đó bạn chỉ cần sửa lại.

Chúng tôi có tất cả 7 quy tắc nhập liệu trong Excel, đó là: Bất kỳ giá trị nào, Số nguyên, Số thập phân, Danh sách, Ngày, Giờ, Độ dài văn bản và Tập quán. Bên trong List là một trong những quy tắc được sử dụng nhiều nhất.

Tuy nhiên, cái gì cũng có hai mặt của nó. Quy tắc nhập này có một hạn chế (đối với các phiên bản Microsoft Excel cũ hơn) là không thể sử dụng danh sách nguồn nếu danh sách nằm trong một Trang tính khác.

// * Chủ đề * //

Được rồi, bây giờ chúng ta hãy làm một ví dụ. Ví dụ, chúng ta có một Workbook với 2 Worksheets có tên là 1 và 2. tương ứng

• Bảng 1 là nơi xác thực dữ liệu được sử dụng.
• Bảng 2 là nơi danh sách nguồn được lưu trữ

Chủ đề là một yêu cầu tạo Data Validation cho khối ô C3:C6 với quy tắc đầu vào là Danh sách. Cụ thể, bạn chỉ có thể chọn Huyện/ TP trực thuộc tỉnh bao gồm trong danh sách:

#đầu tiên. Phương pháp 1: Đặt tên cho danh sách nguồn

+ Bước 1: Lựa chọn Worksheet 2 => chọn khối ô B2:B10 => đặt tên cho khối ô này bằng cách nhập tên bất kỳ vào ô Name Box

Trong phạm vi bài viết này tôi sẽ đặt tên là Huyen => sau đó bấm phím Enter trên bàn phím.

+ Bước 2: Lựa chọn Worksheet 1 => chọn khối ô C3:C6

+ Bước 3: Lựa chọn Data => chọn tiếp theo Data Validation

+ Bước 4: Hộp thoại Data Validation xuất hiện như hình dưới đây:

• Trong Allow bạn chọn là List
• Đánh dấu Ignore blank và In-cell dropdown
• Trong Source bạn nhập =Huyen

=> Tiếp đó hãy chọn OK

Cuối cùng chúng ta nhận được kết quả như hình bên dưới:

Một số bài viết liên quan:

# 2. Phương pháp 2: Sử dụng INDIRECT (ref_text, [a1])

Chìa khóa của phương pháp này là chúng ta sẽ sử dụng hàm INDIRECT(ref_text, [a1]) trong Excel để tham chiếu đến ô hoặc khối ô sẽ sử dụng.

2.1: Các bước thực hiện như sau:

+ Bước 1: Lựa chọn Worksheet 1 => chọn khối ô C3:C6

+ Bước 2: Lựa chọn Data => chọn Data Validation

+ Bước 3: Hộp thoại Data Validation hiện ra:

• Trong Cho phép bạn chọn phong cáchList
• Đánh dấu Ignore blank và In-cell dropdown
• Trong Nguồn nhập khẩu =INDIRECT(“’2’!$B$2:$B$10”)

=> Sau đó bạn chọn OK

2.2. Giải thích công thức = INDIRECT (“’2′! $ B $ 2: $ B $ 10”)

• GIÁN TIẾP(…) là một hàm tham chiếu đến một ô / ô, Trang tính, Sổ làm việc
• “ dấu ngoặc kép được yêu cầu trong trường hợp này bởi vì ’2’!$B$2:$B$10 được coi là dữ liệu chuỗi.
• ‘ dấu ngoặc kép trong trường hợp này có thể được bỏ qua. Tuy nhiên, khi tên Trang tính có dấu cách hoặc là tiếng Việt có dấu thì không xóa được.
• 2 là phần trên cùng của Trang tính có chứa danh sách nguồn
• ! là một ký tự bắt buộc khi tham chiếu đến một ô hoặc khối ô trong một Trang tính khác
• $ B $ 2: $ B $ 10 địa chỉ tuyệt đối của khối ô chứa danh sách nguồn (sau khi chọn ô / khối, nhấn phím F4 trên bàn phím).

# 3. Phương pháp 3: “Quét khối”

+ Bước 1, Bước 2, Bước 3 làm giống như Phương pháp 2 bên trên. Tuy nhiên trong Bước 3 thay vì nhập chúng tôi sẽ “quét khối” trực tiếp. Có thể áp dụng phương pháp này khi “khối quét” nằm trong khu vực nhỏ.

Trong hộp thoại Data Validation lựa chọn

+ Bước 4: Lựa chọn Worksheet 2 => “Khối quét” khối ô B2:B10 => chọn

=> sau đó chọn OK xong.

#4. Phần kết

Được rồi, vậy là tôi đã hướng dẫn bạn cách sử dụng Xác thực dữ liệu khi danh sách nguồn nằm trong Trang tính khác.

Phương pháp 1 (Đặt tên cho danh sách nguồn) và Phương pháp 2 (Sử dụng INDIRECT (ref_text, [a1])) bạn có thể áp dụng cho bất kỳ phiên bản nào của Microsoft Excel (2003, 2007, 2010, 2013, 2016, 2019 và 365).

Phương pháp 3 (“Quét khối”) chỉ hoạt động trên Microsoft Excel 2019 và các phiên bản mới hơn.

Tôi không chắc liệu nó có hoạt động trên phiên bản 2013, 2016 hay không? Vì hiện tại mình chưa sử dụng 2 phiên bản này nên nếu bạn nào đang sử dụng 1 trong 2 phiên bản này thì hãy thông báo cho mình ở phần bình luận bên dưới nhé.

Hy vọng bài viết này sẽ hữu ích với bạn. Xin chào tạm biệt và hẹn gặp lại các bạn trong những bài viết tiếp theo!

CTV: Nhựt Nguyễn – congthucexcel.com

Ghi chú: Bài viết này có hữu ích cho bạn không? Đừng quên đánh giá bài viết, thích và chia sẻ nó với bạn bè và gia đình của bạn!

Những bài viết liên quan : Thủ Thuật Excel

Nguồn : congthucexcel.com

Mọi sự sao chép, trích dẫn vui lòng ghi rõ nguồn: congthucexcel.com

from congthucexcel.com https://ift.tt/3cdhAfm
via Congthucexcel.com