Data validation cách tạo list ở danh sách của sheet khác? (1 người xem)

[mapwaah]

Xin lỗi nếu có ai hỏi về vấn đề này trước mình.
Hiện tại mình đang tìm hiểu về Data Validation.
Mình đang đọc tài liệu trên diễn đàn về nó.
Nhưng hiện tại mình có 1 thắc mắc mong các bạn hỗ trợ giúp.

Vd:
Ở Sheet 1 mình có list {A1,A2,A3} = {Hoa mai,Hoa lan,Hoa phượng}
mình muốn dùng Data Validation để tạo list ở sheet2 nhưng không muốn phải gõ lại mà dùng dữ liệu ở Sheet1.

Trong tài liệu có hướng dẫn tao Name cho vùng dữ liệu ở Sheet1 nhưng mình không hiểu lắm.

Mong các bạn hướng dẫn giúp.

Thanks

 

[leonguyenz]

Trong tài liệu có hướng dẫn tao Name cho vùng dữ liệu ở Sheet1 nhưng mình không hiểu lắm.

Mong các bạn hướng dẫn giúp.

Thanks

Bạn quét chọn A1:A3 ở sheet1, đặt con trỏ vào Name Box (ô nằm trên góc trái, ngang với thanh công thức (Fx)), đặt tên cho Name và nhấn Enter, ví dụ bạn gõ từ DataV, như vậy bạn đã có 1 Name là DataV. Bạn có thể nhấn Ctrl+F3 để xem name này hoặc tạo Name khác.
Việc còn lại chắc là bạn đã biết, chỉ cần đưa Name này vào Source của Data Validation là xong (=DataV).

 

[mapwaah]

Bạn quét chọn A1:A3 ở sheet1, đặt con trỏ vào Name Box (ô nằm trên góc trái, ngang với thanh công thức (Fx)), đặt tên cho Name và nhấn Enter, ví dụ bạn gõ từ DataV, như vậy bạn đã có 1 Name là DataV. Bạn có thể nhấn Ctrl+F3 để xem name này hoặc tạo Name khác.
Việc còn lại chắc là bạn đã biết, chỉ cần đưa Name này vào Source của Data Validation là xong (=DataV).

Mình đã làm như vậy nhưng không được nên mới hỏi bạn ơi.
Làm vậy qua sheet2 gõ DataV nó chỉ hiểu trong list cần tạo có 1 tên là DataV mà thôi

Mình có gõ thử =Sheet1!A2:A4 nhưng nó vẫn không hiểu.

 

[leonguyenz]

Mình đã làm như vậy nhưng không được nên mới hỏi bạn ơi.
Làm vậy qua sheet2 gõ DataV nó chỉ hiểu trong list cần tạo có 1 tên là DataV mà thôi

Mình có gõ thử =Sheet1!A2:A4 nhưng nó vẫn không hiểu.

Bạn thiếu dấu bằng (=).
Trên bài #2 tôi có ghi rõ là: =DataV

 

[mapwaah]

Bạn thiếu dấu bằng (=).
Trên bài #2 tôi có ghi rõ là: =DataV

OK. Thanks bạn.
Mình thiếu dấu =

Ngoài ra bạn có thể chỉ giúp mình thêm 1 vấn đề này không.
Vd như list của mình có 10 dòng nhưng chỉ có 5 loại khác nhau thôi, còn 5 cái còn lại là các dữ liệu trùng.
Khi tạo list trong Data Validation mình có cách nào để nó loại các dữ liệu trùng trong list danh sách ban đầu ngoài cách phải lọc tay trước và chép ra 1 cột khác không ạ?

 

[comet_1701]

OK. Thanks bạn.
Mình thiếu dấu =

Ngoài ra bạn có thể chỉ giúp mình thêm 1 vấn đề này không.
Vd như list của mình có 10 dòng nhưng chỉ có 5 loại khác nhau thôi, còn 5 cái còn lại là các dữ liệu trùng.
Khi tạo list trong Data Validation mình có cách nào để nó loại các dữ liệu trùng trong list danh sách ban đầu ngoài cách phải lọc tay trước và chép ra 1 cột khác không ạ?

Trước khi tạo list cần bỏ giá trị trùng đi nhé
Bạn dùng pivot table trong tab insert (excel 2007)

 

[leonguyenz]

OK. Thanks bạn.
Mình thiếu dấu =

Ngoài ra bạn có thể chỉ giúp mình thêm 1 vấn đề này không.
Vd như list của mình có 10 dòng nhưng chỉ có 5 loại khác nhau thôi, còn 5 cái còn lại là các dữ liệu trùng.
Khi tạo list trong Data Validation mình có cách nào để nó loại các dữ liệu trùng trong list danh sách ban đầu ngoài cách phải lọc tay trước và chép ra 1 cột khác không ạ?

Tạo cột phụ. Tại B2 sheet1 bạn đặt công thức sau, chú ý dòng, bạn có thể nới rộng ra:

=IFERROR(INDEX($A$2:$A$11,MATCH(1,INDEX((COUNTIF($B$1:B1,$A$2:$A$11)=0)*($A$2:$A$11<>""),0),0)),"")

Đặt lại Name Data:

=OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B$2:$B$7,"><"""))

Đưa Name Data vào Data Validation.

 

[mapwaah]

Trước khi tạo list cần bỏ giá trị trùng đi nhé
Bạn dùng pivot table trong tab insert (excel 2007)

Cảm ơn Bạn đã giúp đỡ. Nhưng như vậy là đã tạo thêm dữ liệu rồi.
Mình không muốn tạo thêm cột mới, sheet mới mà chỉ cần thao tác trong các chức năng có sẵn của Data Validation thôi.

 

[leonguyenz]

Cảm ơn Bạn đã giúp đỡ. Nhưng như vậy là đã tạo thêm dữ liệu rồi.
Mình không muốn tạo thêm cột mới, sheet mới mà chỉ cần thao tác trong các chức năng có sẵn của Data Validation thôi.

Nếu dùng công thức mà không dùng cột phụ để lọc duy nhất cho Data Validation thì bạn chờ phiên bản mới anh Bill có cải tiến phần này không nhé.
Giải pháp khác: VBA.

 

[mapwaah]

=OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B$2:$B$7,"><"""))

 

[mapwaah]

Nếu dùng công thức mà không dùng cột phụ để lọc duy nhất cho Data Validation thì bạn chờ phiên bản mới anh Bill có cải tiến phần này không nhé.
Giải pháp khác: VBA.

Cảm ơn Mr.Bum nha.
Mình chỉ muốn đơn giản hóa bảng tính nếu có thể thôi.
Còn nếu không được thì buột phải dùng cách của bạn và Comet_1701 thôi.

Mình đang nghiên cứu cái hàm của bạn

[COLOR=#000000]=IFERROR(INDEX($A$2:$A$11,MATCH(1,INDEX((COUNTIF($B$1:B1,$A$2:$A$11)=0)*($A$2:$A$11<>""),0),0)),"")[/COLOR]

Cái hàm này rất hay nhưng mình chưa hiểu.
Để nghiên cứu thêm nếu vẫn không hiểu thì phiều Mr.Bum giải thích nha.

 

[mapwaah]

Tạo cột phụ. Tại B2 sheet1 bạn đặt công thức sau, chú ý dòng, bạn có thể nới rộng ra:

=IFERROR(INDEX($A$2:$A$11,MATCH(1,INDEX((COUNTIF($B$1:B1,$A$2:$A$11)=0)*($A$2:$A$11<>""),0),0)),"")

Đặt lại Name Data:

=OFFSET(Sheet1!$B$2,,,COUNTIF(Sheet1!$B$2:$B$7,"><"""))

Đưa Name Data vào Data Validation.

Bạn ơi làm ơi giải thích cái hàm này giúp mình với.

=IFERROR(INDEX($A$2:$A$11,MATCH(1,INDEX((COUNTIF($B$1:B1,$A$2:$A$11)=0)*($A$2:$A$11<>""),0),0)),"")

Mình nghiên cứu mà chưa hiểu rõ nữa.

Đặc biệt là đoạn mã

=MATCH(1,INDEX((COUNTIF($B$1:B1,$A$2:$A$11)=0)*($A$2:$A$11<>""),0)

Thanks nhiều nha...

 

[duyangels]

Bạn quét chọn A1:A3 ở sheet1, đặt con trỏ vào Name Box (ô nằm trên góc trái, ngang với thanh công thức (Fx)), đặt tên cho Name và nhấn Enter, ví dụ bạn gõ từ DataV, như vậy bạn đã có 1 Name là DataV. Bạn có thể nhấn Ctrl+F3 để xem name này hoặc tạo Name khác.
Việc còn lại chắc là bạn đã biết, chỉ cần đưa Name này vào Source của Data Validation là xong (=DataV).

Bạn Bum cho mình hỏi, cũng với phương pháp như trên nhưng mình muốn bãy lỗi chi cho phép nhập trong danh sach lít thôi ngoài là báo lỗi. thank bạn trước!

 

[huy.neuk52]

mình làm ở sheet2,lúc chọn vùng mình click sang sheet 1 rồi quét vùng vẫn ra được kết quả validation mà. Có cần phải đặt tên vùng đâu?

 

[comet_1701]

Bạn ơi làm ơi giải thích cái hàm này giúp mình với.

=IFERROR(INDEX($A$2:$A$11,MATCH(1,INDEX((COUNTIF($B$1:B1,$A$2:$A$11)=0)*($A$2:$A$11<>""),0),0)),"")

Mình nghiên cứu mà chưa hiểu rõ nữa.

Đặc biệt là đoạn mã

=MATCH(1,INDEX((COUNTIF($B$1:B1,$A$2:$A$11)=0)*($A$2:$A$11<>""),0)

Thanks nhiều nha...

bạn tách hai hàm này ra dễ xem hơn,

=INDEX((COUNTIF($B$1:B1,$A$2:$A$11)=0)*($A$2:$A$11<>""),0

công thức sẽ ra một mảng {1,1,1,1,1,0,0,0,0,0) trong vùng này 1 xuất hiện đầu tiên thì match (1,{1,1,1,1,1,0,0,0,0,0),0) sẽ là 1
và index($A$2:$A$11,1) là Hoa
tiếp theo {0,1,1,0,1,0,0,0,0,0) 1 này trong vùng xuất hiện đầu tiên ở vị trí thứ hai match(1,{0,1,1,0,1,0,0,0,0,0},0) sẽ trả về là 2, như vậy index($A$2:$A$11,2) là Lan
các cái sau nó cũng tương tự
iferror là dễ bẫy lỗi thôi