Sử dụng data-validation khi danh sách nguồn nằm trong một sheet khác

sử dụng Data-Validation khi list nguồn bên trong một Sheet không giống

sử dụng Data-Validation là 1 trong những cách dễ dàng nhất để áp dụng một nguyên tắc nhập liệu cho 1 dãy dữ liệu. Theo mang định, Excel chỉ cho phép Data-Validation áp dụng những danh sách nguồn bên trong cùng một Sheet cùng với dãy tài liệu sẽ được vận dụng quy tắc này. Mặc dù nhiên, vẫn có phương pháp để lách ngoài chuyện đó.

Bạn đang xem: Sử dụng data-validation khi danh sách nguồn nằm trong một sheet khác

Bạn sẽ xem: áp dụng data-validation khi list nguồn phía bên trong một sheet khác

Chiêu này để giúp bạn tạo cho Data-Validation rất có thể sử dụng những danh sách nguồn nằm trong một Sheet khác. Cách trước tiên là lợi dụng chính bài toán đặt tên cho một dãy của Excel, phương pháp thứ nhì là sử dụng một hàm để call ra danh sách đó.

Cách 1: sử dụng Name mang đến dãy nguồn

Có lẽ cách nhanh nhất và đơn giản nhất để vượt qua ngăn cản Data-Validation của Excel là đặt tên mang đến dãy mà bạn sẽ dùng làm quy tắc nhập liệu. Để biết phương pháp đặt tên đến dãy, bạn xem sinh sống loạt bài này: Sử dụng tên mang đến dãy.

Giả sử bạn đã để tên cho dãy sẽ cần sử dụng làm luật lệ nhập liệu là MyRange. Bạn chọn ô (hoặc dãy) trong ngẫu nhiên Sheet như thế nào mà bạn muốn có một danh sách xổ ra nhằm nhập liệu, rồi trong menu Data bên trên Ribbon, chúng ta chọn Data Tools | Data Validation . Lựa chọn List vào danh sách những Allow, cùng trong khung Source, bạn nhập vào =MyRange. Dấn OK. Bởi vì bạn đã thực hiện một Name để triển khai List, nên chúng ta có thể áp dụng Data-Validation này cho ngẫu nhiên Sheet nào.

*

Nếu thương hiệu Sheet của người tiêu dùng có chứa khoảng tầm trắng, hoặc tất cả dấu giờ đồng hồ Việt, bạn phải để tên Sheet trong một cặp nháy 1-1 ("). Ví dụ, đưa sử tên Sheet chứa danh sách nguồn là Sheet 1 (chứ chưa hẳn Sheet1), thì chúng ta sửa cách làm trên lại như sau: =INDIRECT(""Sheet 1"!$A$1:$A$8"). Chỗ không giống nhau so với cách làm hồi nãy là tất cả thêm một vệt nhấy đơn (") sau vệt nháy kép ("), cùng một dấu nháy đơn (") nữa trước vệt chấm than (!).

Xin mở một ngoặc đơn: trường hợp như hoàn toàn có thể được, khi gặp mặt những tham chiếu đến tên Sheet, bạn nên tập thói quen luôn luôn vứt nó vào vào cặp vết nháy đơn. Điều này, tuy không có ý nghĩa gì với đông đảo tên sheet như Sheet1, DMHH... Nhưng mà nó để giúp đỡ bạn không bao giờ gặp lỗi, khi bạn hay khắc tên Sheet có tầm khoảng trắng, giỏi là gồm bỏ vết tiếng Việt...Ưu điểm cùng Khuyết điểm của tất cả hai phương pháp đã nêu trên

Đặt tên mang lại dãy, và sử dụng hàm INDIRECT, đều phải sở hữu cái thuận lợi và cả mẫu bất tiện.

Tiện lợi của câu hỏi đặt tên cho dãy, là vấn đề bạn biến hóa tên Sheet chẳng có tác động gì mang lại Data-Validation. Cùng đó chính là cái phiền toái của vấn đề dùng INDIRECT, khi bạn đổi thương hiệu Sheet, tên bắt đầu sẽ không auto cập nhật trong cách làm dùng INDIRECT, vì thế nếu vẫn mong mỏi dùng phương pháp này, chúng ta phải mở Data-Validation ra và sửa lại thương hiệu Sheet vào công thức.

Tiện lợi của việc dùng INDIRECT, là dãy cần sử dụng làm list nguồn của bạn luôn luôn luôn nằm yên chỗ đã lựa chọn (A1:A8 trong lấy ví dụ như trên chẳng hạn). Còn nếu như khách hàng dùng Name, mà bạn lỡ tay xóa mất vài sản phẩm (hoặc cột) ngay vị trí đựng Name, thì bạn phải kiểm soát và điều chỉnh lại mang đến đúng...

Điều khiển Conditional Formating bằng checkbox.

mặc dù Conditional Formating là 1 trong những chiêu mạnh mẽ của Excel, nhưng hy vọng bật giỏi tắt nó bởi ribbon hay menu thì tương đối bực bội. Hiện nay ta biến hóa hoá bằng phương pháp điều khiển bằng 1 checkbox giống hệt như 1 công tắc (hoặc 1 chiếc toggle Button càng kiểu như hơn).

Conditional Formating có từ đời Excel 97, gán format cho các ô nào thoả 1 số điều kiện nào đó. Điều kiện có thể là 1 điều kiện về giá bán trị, tuy thế ta hoàn toàn có thể tuỳ biến nhiều hơn khi dùng điều kiện là công thức, phụ thuộc vào đó ta có thể chuyển đổi định dạng cho phần nhiều ô này, khi tất cả sự biến đổi giá trị của ô khác.

Xem thêm: Cây Tài Lộc Có Ý Nghĩa Gì Và Cách Chăm Sóc Cây Tài Lộc ), Cách Chăm Sóc Cây Tài Lộc

1. Sử dụng 1 Checkbox hoặc 1 Toggle Button nhằm xem với ẩn dữ liệu:

Bạn muốn một vùng tài liệu nào đó chỉ chỉ ra lúc buộc phải xem, xem chấm dứt thì đổi thay đi mang lại rảnh. Trước tiên chúng ta phải gán lên sheet 1 Checkbox hoặc 1 Toggle Button. Trong Excel 2010, vào tab Developer, nhận Insert trong Controls - chọn Checkbox hoặc Toggle Button trong Control Toolbox, vào Excel 2003 chọn trong view – Toolbar – Control Toolbox, vẽ lên sheet 1 cái. Trong hình, tôi làm thử 2 cái.


*

Nhấn vào nút design, click chọn chiếc control các bạn vừa vẽ, dìm thêm nút Property. Trong hành lang cửa số Property, sửa chiếc Caption thành View/ Hide, sửa loại Linked Cell thành $C$2. (cả 2010 cùng 2003 như nhau, cả checkbox và Toggle Button như nhau).


*

Bây giờ khi bạn click lựa chọn checkbox hoặc thừa nhận nút Toggle, ô C2 sẽ lần lượt có các giá trị TRUE với FALSE.


Bây giờ giả sử vùng dữ liệu của chúng ta gồm 4 fields, trong những số đó bạn chỉ muốn 3 fields hiện thường xuyên, còn field trang bị 4 thì lúc nào cần mới hiện ra nhằm xem, không đề xuất thì lốt đi. Bạn khắc ghi chọn vùng đựng field 4, vào 2010 chúng ta vào tab Home, Conditional Formating, New Rule, chọn tiếp “use a formula to determine which cells lớn format”, vào 2003 là Fornat - Conditional Formating - chọn tiếp “Formula is”. Trong ô kế bến, bạn gõ: = $C$2=FALSE.


Nếu các bạn không mê thích thì định hình ô C2 chữ trắng luôn, nhằm khỏi thấy chữ TRUE, FALSE hiện nay lên.

2. Tắt mở định hình màu mang lại ô:

Dùng Conditional Formating nhằm mục tiêu tô màu ô theo điều kiện giúp ta dễ tìm được những ô có giá trị đặc biệt cho trước. Excel 2010 có nhiều định dạng khác nhau cho quý hiếm số nằm trong khoảng cho trước. Nhưng phương án để mở tắt bởi checkbox là không có sẵn.

Tương trường đoản cú như phần trên, ta tạo ra 1 checkbox hoặc 1 Toggle Button links tới ô $C$2. Nhưng lần này ta đặt name mang đến nó là IsFill chẳng hạn. Ta cũng để name cho ô $A$2 là BeginNum với $B$2 là EndNum, với A2 là số lượng giới hạn dưới thí dụ 100, với B2 là giới hạn trên tỉ dụ 1.000.

Trong vùng tài liệu B5:B16, ta muốn giá trị như thế nào nằm trong tầm BeginNum và EndNum sẽ tiến hành tô màu. Vậy sử dụng conditional Formating như trên, lựa chọn vùng C8:C18, lần này phương pháp là: