Relational Lookup

[lifeson]

Not sure if the thread title describes what I want to do but here goes:

I have a workbook with 3 sheets:

Data - this conmtains all the unique data about employees eg, pay no, first, surname addresss etc

Course - this contains details about a specific training course and type

EmpCourse, this shows a list of employee payno and the course they attended on which date

What I want to do is on a form when a particular course is selected, t shows the unique details about that course (done that ) but I also want it to show a list of all the people who have attended that course.
I can get it to list the pay numbers of the people but I also want it to look up the name (column B) based on the pay number from the data table

I also need to be able to export a list of the paynumbers shown in the results list and create a new sheet with their names and addresses to use as a mailing list

Any help greatly appreciated.

[Bob Phillips]

Change this

[vba]

Set rCopy = Range(.Cells(2, 1), .Cells(2, 1).End(xlToRight).End(xlDown))
[/vba]

to

[vba]

Set rCopy = Range(.Cells(2, 1), .Cells(2, 1).End(xlToRight).End(xlDown)).SpecialCells(xlCellTypeVisible)
[/vba]

[Bob Phillips]

Also, change

[vba]

dCount = WorksheetFunction.Count(Range("A:A"))-1
[/vba]

to

[vba]

dCount = WorksheetFunction.CountIf(Range("B:B"), cboCourseID.Value)
[/vba]

[rory]

Here's a revised file showing a couple of changes. I've added the employee names and the data from column N of the Data sheet (currently blank) to the list box, so you can use this for an export function. Hopefully you can adjust this to fit your real workbook!

[lifeson]

Change this

[vba]

Set rCopy = Range(.Cells(2, 1), .Cells(2, 1).End(xlToRight).End(xlDown))
[/vba]

to

[vba]

Set rCopy = Range(.Cells(2, 1), .Cells(2, 1).End(xlToRight).End(xlDown)).SpecialCells(xlCellTypeVisible)
[/vba]

Thanks XLD (again )
That bit "SpecialCells(xlCellTypeVisible)" will be particulary useful as at the moment I keep copyng the autofilter results to a seperate sheet to use.

[lifeson]

Here's a revised file showing a couple of changes. I've added the employee names and the data from column N of the Data sheet (currently blank) to the list box, so you can use this for an export function. Hopefully you can adjust this to fit your real workbook!

Thanks Rory
That was exactly what I was looking for in regards to the list box results

Now that problem has been resolved is it possible that a list could be created of employees who have not attended a training course?
I should imagine that would be a lot harder to do with the way the data has been set up
Would I have been better adding each course as a column in the main data page?
but what I know from dealing with access you should set the data up with a seperate page/table for each set of unique data and a sheet for relational data.
Does this still hold true for Excel?

[Bob Phillips]

.

[rory]

Well, Excel is not really a database program, although it has some database capabilities; it is certainly not a relational database.
However, I don't think there's too much wrong with your setup - you could ad a column to your employee data list that counts (using COUNTIF) how many times the ID appears in the course/ID list, then just filter for those where the count is 0.
Regards,
Rory