Cách Tạo Dropdown List Phụ Thuộc Trong Google Sheets [2026]

Vấn đề: dropdown thường bị "vô dụng" khi list quá dài

Bạn có 63 tỉnh thành, mỗi tỉnh vài chục quận huyện. Làm một cột "Tỉnh" và một cột "Quận/Huyện" bằng data validation thông thường thì cột thứ hai sẽ hiện ra toàn bộ hơn 700 quận huyện của cả nước, bất kể người dùng vừa chọn "Hà Nội" hay "Cà Mau". Người nhập liệu phải cuộn tìm, dễ chọn nhầm, và dữ liệu thu về gần như không dùng được cho báo cáo. Đây chính là lý do dropdown phụ thuộc (cascading dropdown) tồn tại: giá trị dropdown thứ hai được lọc lại dựa trên giá trị người dùng vừa chọn ở dropdown thứ nhất.

Cách làm phổ biến nhất hiện nay dùng hàm INDIRECT kết hợp Named Range. Ngoài ra còn hai cách khác đáng biết: dùng FILTER (nhanh, hiện đại, nhưng không dùng được cho data validation trực tiếp) và dùng Apps Script (linh hoạt nhất nhưng tốn công setup). Bài này đi qua cả ba, kèm những chỗ hay gãy giữa chừng mà nhiều người mất cả buổi mới tìm ra.

Cách 1: Named Range + INDIRECT — chuẩn nhất cho data validation

Đây là cách Google khuyến nghị chính thức và cũng là cách duy nhất tạo ra dropdown "thật" (có mũi tên, click chọn được) mà vẫn phụ thuộc lẫn nhau.

Bước 1: Chuẩn bị dữ liệu nguồn

Tạo một sheet riêng tên "Data" (ẩn sau khi xong), mỗi cột là một danh mục cha, hàng đầu là tên danh mục, các hàng dưới là danh sách con tương ứng. Ví dụ cột A tên "HaNoi" chứa các quận: Ba Đình, Hoàn Kiếm, Cầu Giấy...; cột B tên "DaNang" chứa: Hải Châu, Thanh Khê, Sơn Trà...

Điểm mấu chốt hay bị bỏ qua: tên cột (cũng là tên Named Range sau này) không được chứa dấu cách hoặc ký tự đặc biệt. "Hà Nội" phải viết thành "HaNoi", "TP HCM" thành "TPHCM". Google Sheets không cho đặt tên range có khoảng trắng, và đây là lỗi số một khiến INDIRECT trả về lỗi #REF!.

Bước 2: Tạo Named Range cho từng danh sách con

• Bôi đen toàn bộ danh sách con của một tỉnh (không lấy dòng tiêu đề)
• Vào Data → Named ranges → đặt tên trùng khớp với giá trị sẽ xuất hiện ở dropdown cha (ví dụ "HaNoi")
• Lặp lại cho từng tỉnh — nếu có 63 tỉnh thì phải tạo 63 named range, khá tốn công nhưng làm một lần

Mẹo rút gọn: bôi đen toàn bộ bảng dữ liệu (cả tiêu đề lẫn nội dung) rồi vào Data → Named ranges → dùng tính năng tạo hàng loạt theo tiêu đề cột nếu phiên bản Sheets của bạn hỗ trợ (Data → Split text to columns không liên quan, đừng nhầm). Nếu không có, cách nhanh hơn là viết một đoạn Apps Script để loop tạo named range tự động — tương tự cách bạn từng dùng Apps Script để tạo menu tùy chỉnh trong Google Sheets, chỉ khác là gọi SpreadsheetApp.getActiveSpreadsheet().setNamedRange() thay vì tạo menu.

Bước 3: Dropdown cha (cột Tỉnh)

Chọn cột A2:A1000 → Data → Data validation → Criteria: "Dropdown (from a range)" hoặc "List from a range" → trỏ tới hàng tiêu đề của bảng Data (chính là danh sách tên tỉnh).

Bước 4: Dropdown con (cột Quận/Huyện) dùng INDIRECT

Chọn ô B2 → Data validation → Criteria: List from a range → nhập:

=INDIRECT(SUBSTITUTE(A2," ",""))

Hàm SUBSTITUTE ở đây để phòng trường hợp giá trị hiển thị ở cột A vẫn có dấu cách ("Hà Nội") trong khi named range đặt tên không dấu cách ("HaNoi") — bạn cần tự đồng bộ quy tắc đặt tên, thường là bỏ dấu cách và bỏ dấu tiếng Việt. Nếu list tỉnh của bạn có dấu tiếng Việt đầy đủ, cách xử lý sạch nhất là thêm một cột ẩn chuyển đổi tên tỉnh sang dạng không dấu bằng công thức, rồi INDIRECT trỏ vào cột ẩn đó thay vì xử lý trực tiếp trong INDIRECT.

Sau khi nhập công thức cho B2, copy xuống cả cột B (Ctrl+C rồi paste vào B3:B1000) để mọi dòng đều có dropdown phụ thuộc riêng theo giá trị A tương ứng.

Lỗi thường gặp và cách sửa

Triệu chứng	Nguyên nhân	Cách sửa
Dropdown con trống hoàn toàn	Named range chưa tồn tại hoặc sai tên	Kiểm tra Data → Named ranges, đối chiếu chính xác với giá trị SUBSTITUTE trả về
Báo lỗi "Invalid range"	Tên named range chứa dấu cách/ký tự đặc biệt	Đổi tên named range, bỏ hết dấu cách và ký tự có dấu
Dropdown con hiện đúng nhưng không lọc theo dòng	Công thức INDIRECT trỏ cố định A2 thay vì tương đối	Copy lại từ ô gốc bằng Ctrl+C/Paste, không gõ tay từng ô
Sau khi thêm tỉnh mới, dropdown con vẫn cũ	Named range mới chưa được tạo, hoặc dropdown cha chưa refresh nguồn	Tạo named range cho tỉnh mới, xóa và tạo lại data validation cho cột cha nếu cần

Một lỗi khác ít người để ý: nếu bạn từng làm việc với range màu xen kẽ để dễ đọc dữ liệu tỉnh/huyện, và range đó bị convert sai định dạng khi copy qua sheet mới, dropdown vẫn hoạt động bình thường vì data validation không phụ thuộc màu — nhưng nếu bạn gặp lỗi convert range màu ở bước chuẩn bị dữ liệu, tham khảo thêm cách khắc phục lỗi không convert được range màu xen kẽ trong Google Sheets để tách biệt hai vấn đề.

Cách 2: Dùng FILTER khi không cần dropdown "chuẩn" mà cần hiển thị động

INDIRECT có một giới hạn: nó chỉ hoạt động tốt với danh sách tĩnh, cố định theo cột named range đã tạo sẵn. Nếu dữ liệu quận/huyện của bạn đang nằm trong một bảng dài một cột (ví dụ cột A là tỉnh, cột B là huyện, mỗi dòng một cặp), việc tạo hàng chục named range là dư thừa. Lúc này FILTER gọn hơn nhiều:

=FILTER(DanhSach!B:B, DanhSach!A:A = A2)

Vấn đề là FILTER trả ra một mảng, không thể gán trực tiếp làm nguồn cho Data Validation kiểu "List from a range" theo từng ô riêng lẻ vì Google Sheets validation không tự động re-evaluate theo dòng khi dùng công thức mảng kiểu này trong hộp thoại validation. Cách khắc phục thực tế: tạo một cột phụ ẩn (ví dụ cột Z) chứa công thức FILTER cho từng dòng, rồi trỏ Data validation của cột B vào range động đó bằng INDIRECT trỏ tới cột Z tương ứng. Nói cách khác, FILTER giải quyết bài toán "lọc dữ liệu động không cần named range", nhưng vẫn cần INDIRECT làm cầu nối để đưa vào dropdown thật. Cách này hợp khi số lượng danh mục cha thay đổi thường xuyên (bạn thêm bớt tỉnh, huyện liên tục) vì không phải tạo lại named range mỗi lần.

Cách 3: Apps Script khi cần dropdown 3 cấp hoặc dữ liệu lấy từ nguồn ngoài

INDIRECT xử lý tốt 2 cấp (Tỉnh → Huyện). Nhưng nếu bạn cần 3 cấp (Tỉnh → Huyện → Xã) với vài nghìn dòng dữ liệu, số named range cần tạo tăng theo cấp số nhân và việc bảo trì trở thành ác mộng — mỗi lần cập nhật danh sách xã của một huyện, bạn phải sửa named range đúng huyện đó.

Giải pháp thực tế hơn ở quy mô này là dùng onEdit trigger trong Apps Script: khi người dùng đổi giá trị cột Tỉnh, script tự động set lại data validation của ô Huyện tương ứng bằng SpreadsheetApp.newDataValidation().requireValueInList(), và tương tự cho cấp Xã khi Huyện thay đổi. Code mẫu rút gọn:

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  if (sheet.getName() !== "Data Entry" || range.getColumn() !== 1) return;

  const tinh = range.getValue();
  const huyenList = getHuyenByTinh(tinh); // đọc từ sheet dữ liệu nguồn
  const huyenCell = sheet.getRange(range.getRow(), 2);
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(huyenList, true)
    .build();
  huyenCell.setDataValidation(rule);
  huyenCell.clearContent();
}

Cách này nặng hơn nhưng đổi lại linh hoạt tuyệt đối: dữ liệu nguồn có thể lấy từ API ngoài, từ một sheet khác cập nhật hàng ngày, hoặc filter theo nhiều điều kiện cùng lúc chứ không chỉ một cấp cha-con. Nếu bạn đã quen viết Apps Script để tạo menu tùy chỉnh, phần này không khó thêm bao nhiêu — chỉ là đổi từ xử lý menu sang xử lý sự kiện edit.

Khi nào chọn cách nào

Với 2 cấp, dưới 50 danh mục cha, dữ liệu ít thay đổi: dùng INDIRECT + Named Range, setup một lần rồi thôi, không cần code. Với dữ liệu thay đổi thường xuyên hoặc lấy từ bảng dài không muốn tạo named range thủ công: FILTER kết hợp cột phụ ẩn. Với 3 cấp trở lên, hoặc cần logic lọc phức tạp (ví dụ loại trừ giá trị đã chọn ở dòng khác): Apps Script với onEdit trigger.

Một điểm dễ bị bỏ qua: dropdown phụ thuộc chỉ có giá trị thực sự khi nó nằm trong một hệ thống nhập liệu có kiểm soát — tức là bảng dữ liệu sẽ được tổng hợp, lọc, hoặc đưa vào dashboard sau này. Nếu bạn đang xây một file quản lý dự án hay theo dõi khách hàng có cột tỉnh/thành, việc dropdown lọc đúng ngay từ khâu nhập liệu giúp bước tổng hợp sau này (ví dụ dùng PivotTable hay dựng dashboard quản lý dự án bằng Google Sheets) không phải xử lý dữ liệu rác do người nhập chọn nhầm huyện của tỉnh khác.

Vài lỗi vặt khác khi triển khai thực tế

Cột Quận/Huyện sau khi đổi Tỉnh vẫn giữ giá trị cũ (ví dụ đang là "Cầu Giấy" của Hà Nội, đổi Tỉnh sang Đà Nẵng thì Cầu Giấy vẫn còn nguyên dù không hợp lệ). Data validation của Google Sheets không tự xóa giá trị cũ khi nguồn dropdown thay đổi — đây là hành vi mặc định, không phải lỗi. Nếu muốn tự động xóa, phải dùng Apps Script như cách 3 ở trên, thêm dòng huyenCell.clearContent().

Một tình huống khác: khi copy công thức INDIRECT xuống hàng loạt rồi paste dữ liệu từ Excel vào, đôi khi các số 0 đầu trong mã tỉnh/mã huyện bị Sheets tự động cắt bỏ, dẫn đến việc so khớp giữa dropdown cha và tên named range bị lệch. Nếu cột mã của bạn có định dạng dạng "001", "024" mà bị Sheets chuyển thành "1", "24", nên format cột đó thành Plain Text trước khi nhập, hoặc xem thêm cách giữ số 0 đầu trong ô Google Sheets để tránh sai lệch dữ liệu tham chiếu.

Câu hỏi thường gặp

Dropdown list phụ thuộc trong Google Sheets là gì?

Là 2 hoặc nhiều ô dropdown liên kết với nhau, trong đó danh sách lựa chọn ở ô thứ hai thay đổi theo giá trị đã chọn ở ô thứ nhất. Ví dụ chọn Tỉnh/Thành ở cột A, cột B chỉ hiện Quận/Huyện thuộc tỉnh đó.

Cần dùng hàm gì để tạo dropdown phụ thuộc?

Chủ yếu dùng hàm INDIRECT kết hợp Named Range và Data Validation. INDIRECT sẽ trỏ tới dải ô có tên trùng với giá trị đã chọn ở dropdown cấp trên, không cần viết Apps Script.

Named Range trong dropdown phụ thuộc phải đặt tên như thế nào?

Tên Named Range phải trùng chính xác với giá trị trong dropdown cấp 1 (không dấu cách, không ký tự đặc biệt). Ví dụ dropdown cấp 1 có giá trị 'Ha_Noi' thì Named Range chứa danh sách quận huyện của Hà Nội cũng phải đặt tên 'Ha_Noi'.

Tạo dropdown phụ thuộc 3 cấp có được không?

Được, chỉ cần lặp lại cách làm: mỗi cấp dùng INDIRECT trỏ tới Named Range tương ứng với giá trị cấp trước đó. Ví dụ Tỉnh/Thành → Quận/Huyện → Phường/Xã, mỗi cấp cần một bộ Named Range riêng.