Tạo danh sách từ một cột của sheet khác

[endynd]

Xin chào anh em!
Mình muốn tạo 1 danh sách ở 1 ô (có nút trổ xuống để chọn).
Dữ liệu lấy từ một cột ở shêt khác. Shêt đó dùng để nhập dữ liệu

Mô tả:
giả sử có 2 shêt : shet 1 là để nhập dữ liệu. shet 2 để tìm kiếm nhân viên

1. ở một ô trong shet 2 mình muốn tạo 1 danh sách trổ xuống (lấy dữ liệu ở cột B shet 1). khi chọn mã nhân viên: giả sử là 101 thì nó hiển thị tất cả thông tin của nhân viên đó

2. muốn tìm theo tên. đánh tên của nhân viên đó, sẽ cho ta tất cả thông tin về nhân viên đó.

Các bạn giúp mình ! Sau đó mình sẽ phát triển tiếp
Cảm ơn nhiều

 

[nghiaphuc]

Xin chào anh em!
Mình muốn tạo 1 danh sách ở 1 ô (có nút trổ xuống để chọn).
Dữ liệu lấy từ một cột ở shêt khác. Shêt đó dùng để nhập dữ liệu
Mô tả:
giả sử có 2 shêt : shet 1 là để nhập dữ liệu. shet 2 để tìm kiếm nhân viên
1. ở một ô trong shet 2 mình muốn tạo 1 danh sách trổ xuống (lấy dữ liệu ở cột B shet 1). khi chọn mã nhân viên: giả sử là 101 thì nó hiển thị tất cả thông tin của nhân viên đó
2. muốn tìm theo tên. đánh tên của nhân viên đó, sẽ cho ta tất cả thông tin về nhân viên đó.
Các bạn giúp mình ! Sau đó mình sẽ phát triển tiếp
Cảm ơn nhiều

Trước hết bạn tạo 1 Name động như sau:
MaNV=OFFSET(DL!$B$4,,,COUNTA(DL!$B:$B)-1,1)
Sau đó, trên Sheet QL:
- Tại cột B (Mã NV), bạn sử dụng Data Validation để chọn dữ liệu (trong file đính kèm, bạn chọn Data\Validation sẽ biết cách sử dụng)
- Tại các cột C, D, E, bạn có thể sử dụng hàm VLOOKUP để tra thông tin. Chẳng hạn:
C6=IF($B6="","",VLOOKUP($B6,OFFSET(MaNV,0,0,,4),COLUMN()-1,0)) (sau đó fill công thức cho các ô còn lại tại các cột C, D, E).

Chúc thành công!

 

[dat_butmuc]

Trước hết bạn tạo 1 Name động như sau:
MaNV=OFFSET(DL!$B$4,,,COUNTA(DL!$B:$B)-1,1)
Sau đó, trên Sheet QL:
- Tại cột B (Mã NV), bạn sử dụng Data Validation để chọn dữ liệu (trong file đính kèm, bạn chọn Data\Validation sẽ biết cách sử dụng)
- Tại các cột C, D, E, bạn có thể sử dụng hàm VLOOKUP để tra thông tin. Chẳng hạn:
C6=IF($B6="","",VLOOKUP($B6,OFFSET(MaNV,0,0,,4),COLUMN()-1,0)) (sau đó fill công thức cho các ô còn lại tại các cột C, D, E).

Chúc thành công!

Với bảng tính bố trí giống nhau vầy thì chỉ cần một công thức tại C6 này cũng được bạn Phúc ơi?

=IF($B6="","",VLOOKUP($B6,DL!$B$4:$F$18,COLUMNS($B:C),))

 

[nghiaphuc]

Với bảng tính bố trí giống nhau vầy thì chỉ cần một công thức tại C6 này cũng được bạn Phúc ơi?

=IF($B6="","",VLOOKUP($B6,DL!$B$4:$F$18,COLUMNS($B:C),))

Trong trường hợp nhập thêm nhân viên tại Sheet DL thì công thức trên hình như không ổn đâu. Chẳng lẽ cứ nhập thêm 1 nhân viên lại phải chuyển sang sửa lại công thức (tham số thứ 2 của VLOOKUP)?

 

[dat_butmuc]

Trong trường hợp nhập thêm nhân viên tại Sheet DL thì công thức trên hình như không ổn đâu. Chẳng lẽ cứ nhập thêm 1 nhân viên lại phải chuyển sang sửa lại công thức (tham số thứ 2 của VLOOKUP)?

Bạn Phúc đang nói đến cái này

DL!$B$4:$F$18

chứ gì?
Nếu là cái này thì là chỉ mô phỏng/ví dụ trong trường hợp này thôi, chứ giãn dòng ra thì chẳng sao mà?
Ví dụ lấy nguyên mảng:

DL!$B$4:$F$1800

chẳng hạn hay lấy lớn hơn nữa?

 

[endynd]

Trước hết bạn tạo 1 Name động như sau:
MaNV=OFFSET(DL!$B$4,,,COUNTA(DL!$B:$B)-1,1)
Sau đó, trên Sheet QL:
- Tại cột B (Mã NV), bạn sử dụng Data Validation để chọn dữ liệu (trong file đính kèm, bạn chọn Data\Validation sẽ biết cách sử dụng)
- Tại các cột C, D, E, bạn có thể sử dụng hàm VLOOKUP để tra thông tin. Chẳng hạn:
C6=IF($B6="","",VLOOKUP($B6,OFFSET(MaNV,0,0,,4),COLUMN()-1,0)) (sau đó fill công thức cho các ô còn lại tại các cột C, D, E).

Chúc thành công!

Cảm ơn anh nhé!
Nhưng còn vấn đề thứ 2, anh giải quyết ntn?
2. muốn tìm theo tên. đánh tên của nhân viên đó, sẽ cho ta tất cả thông tin về nhân viên đó.(dòng thứ 3 đó anh)

 

[nghiaphuc]

Cảm ơn anh nhé!
Nhưng còn vấn đề thứ 2, anh giải quyết ntn?
2. muốn tìm theo tên. đánh tên của nhân viên đó, sẽ cho ta tất cả thông tin về nhân viên đó.(dòng thứ 3 đó anh)

Trường hợp này có lẽ phải dùng đến VBA. Bạn sử dụng code sau cho Sheet QL:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$H$2" Then Exit Sub
    [G6:K65536].Clear
    Sheet1.Range(Sheet1.[A3], Sheet1.[A65536].End(xlUp)).Resize(, 5).AdvancedFilter xlFilterCopy, [J1:J2], [G5:K5]
End Sub

Trong file đính kèm, khi bạn nhập tên vào ô H2, tất cả nhân viên có tên vừa nhập sẽ được lọc vào bảng phía dưới.

 

[Lê Duy Thương]

bạn phúc đang nói đến cái này chứ gì?
Nếu là cái này thì là chỉ mô phỏng/ví dụ trong trường hợp này thôi, chứ giãn dòng ra thì chẳng sao mà?
Ví dụ lấy nguyên mảng: Chẳng hạn hay lấy lớn hơn nữa?

đối với vlookup tham sỐ thứ 2 tôi vẫn hay dùng thế này mà chưa phát hiện máy chạy chậm bao giờ
=if($b6="","",vlookup($b6,dl!$b:$f,column()-1,0))
khỏi mất công điều chỉnh, tuy nhiên chọN vùng dữ liệu thì có vẻ đẹp hơn

 

[ngh_khanh]

Trường hợp này có lẽ phải dùng đến VBA. Bạn sử dụng code sau cho Sheet QL:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$H$2" Then Exit Sub
    [G6:K65536].Clear
    Sheet1.Range(Sheet1.[A3], Sheet1.[A65536].End(xlUp)).Resize(, 5).AdvancedFilter xlFilterCopy, [J1:J2], [G5:K5]
End Sub

Trong file đính kèm, khi bạn nhập tên vào ô H2, tất cả nhân viên có tên vừa nhập sẽ được lọc vào bảng phía dưới.

Em thử file của bác không chạy được, nên em làm thế này:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$B$3" Then Exit Sub
    Range("A6:E65536").Clear
    With Sheet1.Range(Sheet1.[A3], Sheet1.[A65536].End(xlUp)).Resize(, 5)
        .AutoFilter 2, Range("B3")
        .Offset(1, 2).Resize(, 3).SpecialCells(12).Copy Range("A6")
        .AutoFilter
    End With
End Sub

 

[nghiaphuc]

Em thử file của bác không chạy được, nên em làm thế này:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$3" Then Exit Sub
Range("A6:E65536").Clear
With Sheet1.Range(Sheet1.[A3], Sheet1.[A65536].End(xlUp)).Resize(, 5)
.AutoFilter 2, Range("B3")
.Offset(1, 2).Resize(, 3).SpecialCells(12).Copy Range("A6")
.AutoFilter
End With
End Sub

Ý của tác giả là tìm theo tên chứ nếu tìm theo mã thì cần gì code đâu bạn, chỉ cần dùng VLOOKUP là đủ rồi.
Trong file của mình, bạn đã thử nhập tên vào ô H2 chưa? (nhập tên A, B, C,...). Chú ý là nếu bạn kéo hộp Comment đi, bạn sẽ thấy có một bảng tiêu chuẩn lọc nằm tại vùng J1:J2. Có thể do bạn xóa cột nên bảng tiêu chuẩn này không nằm đúng vị trí cũ dẫn đến câu lệnh AdvancedFilter trên không chạy được.

 

[endynd]

Ý của tác giả là tìm theo tên chứ nếu tìm theo mã thì cần gì code đâu bạn, chỉ cần dùng VLOOKUP là đủ rồi.
Trong file của mình, bạn đã thử nhập tên vào ô H2 chưa? (nhập tên A, B, C,...). Chú ý là nếu bạn kéo hộp Comment đi, bạn sẽ thấy có một bảng tiêu chuẩn lọc nằm tại vùng J1:J2. Có thể do bạn xóa cột nên bảng tiêu chuẩn này không nằm đúng vị trí cũ dẫn đến câu lệnh AdvancedFilter trên không chạy được.

Đúng rồi! khi số lượng nhân viên lên đến hàng trăm người. mình chỉ cần nhập tên và sẽ cho ta tất cả các thông tin của những nhân viên có tên như vậy và hiển thị xuống bảng.