Các hộp thả xuống phụ thuộc theo tầng là một thủ thuật thú vị và phổ biến, cho phép danh sách thả xuống thứ hai thay đổi các tùy chọn của nó dựa trên lựa chọn được thực hiện trong hộp thả xuống đầu tiên. Điều này thường được thực hiện bằng cách sử dụng hàm INDIRECT ().

Một thủ thuật phổ biến khác là sử dụng công thức Phạm vi được đặt tên động để tạo phạm vi được đặt tên tự điều chỉnh khi bạn thêm các mục vào cột. Rất hữu dụng.

ĐẶT VẤN ĐỀ: * Hai “thủ thuật” này không kết hợp với nhau. Nếu bạn sử dụng công thức Phạm vi được đặt tên động để tạo danh sách Nhóm và sau đó sử dụng phạm vi được đặt tên đó làm nguồn của danh sách DV trong ô A1, bạn không thể sử dụng phương pháp INDIRECT (A1) để chọn phạm vi được đặt tên phụ thuộc có cùng tên như văn bản đã chọn trong A1.

GIẢI PHÁP: * Cách giải quyết sau đó là hoàn toàn không tạo công thức phạm vi được đặt tên động. Thay vào đó, bạn di chuyển tất cả hoạt động động vào công thức “Nguồn” xác thực dữ liệu phụ thuộc.

THIẾT LẬP:

1.Trên trang Rosters tất cả các danh sách của bạn sẽ nằm cạnh nhau trong các cột, hãy thiết lập chúng như vậy:

1

{trống} 2. Chúng tôi tạo một phạm vi được đặt tên làAnchorCell bằng cách nhấp vào A1 và nhập tên đó vào hộp tên như hình trên.

Điều này cho phép chúng tôi tạo công thức xác thực dữ liệu sau này vẫn hoạt động trên Excel 2003.

3.Chúng tôi tạo một dải động có tên làTeams bằng cách nhấn CTRL-F3 và xác định tên bằng công thức RefersTo của:

= OFFSET (Phân nhóm! $ A $ 1,,, 1, COUNTA (Phân nhóm! $ 1: $ 1))

2

Điều này cho phép bạn thêm các cột mới (Nhóm) bất cứ lúc nào bạn muốn mà không cần phải thay đổi bất kỳ điều gì khác, tất cả sẽ tiếp tục hoạt động và bao gồm các nhóm mới của bạn.

LƯU Ý: Không có cột trống, đây là trang tính tham khảo, hãy giữ nó ngăn nắp.

{trống} 4. Tiếp theo, không có gì lạ ở đây, chúng tôi sử dụng rangeTeamsas được đặt tên làm nguồn danh sách cho cột A xác thực dữ liệu chính của chúng tôi trên trang TheSelices *:

3

Sau khi áp dụng, nó cung cấp danh sách các đội từ hàng 1 của trang Bảng phân công của chúng tôi:

4

{trống} 5. Và đây là điều kỳ diệu. Công thức danh sách Xác thực Dữ liệu trong B2 thực hiện tất cả các công việc nặng nhọc, sử dụng hàm OFFSET () và MATCH để tìm nhóm được chọn trong cột A trên hàng 1 của trang TheRosters *, sau đó tạo danh sách thả xuống chỉ gồm các mục trong cột đó. Trong B2, công thức DV sẽ là:

OFFSET (AnchorCell, 1, MATCH ($ A2, Nhóm, 0) -1, COUNTA (OFFSET (AnchorCell,, MATCH ($ A2, Nhóm, 0) -1, 50, 1)) – 1, 1) Bạn nên dành chút thời gian đọc các file trợ giúp về offset để các thông số có ý nghĩa với bạn:

= OFFSET (tham chiếu, hàng, cột, [chiều cao], [chiều rộng])

{trống} 6. Sau khi được áp dụng, danh sách phụ sẽ tự tạo dựa trên lựa chọn được thực hiện trong ô cột A:

5