Generating A Report??

**GazNicki** · 11-30-2010, 07:08 AM

Hi all.

I am making a Training Matrix for the company I have just started working for. I want to make a good impression, and since hardly anything here is electronically stored I want to create a more indepth Matrix than just simple tabbed data.

I have my matrix setup now, it will allow upto 200 staff and upto 120 training modules - more than enough considering it can be replicated into departments.

My layout is as so:

Cells A1, B1, C1, A2, A3 are blank and not used.

Cells D1 - DS1 are the titles for the traning modules.
Cells D2 - DS2 are the Document Reference Numbers. (Cell B2 is the title)
Cells D3 - DS3 are the Document Version Numbers. (Cell B3 is the title)
Cells D4 - DS4 are the Document Issue Date. (Cell B4 is the title)

Cells A5 - DS5 are coloured grey as cells A4 and B4 contain the titles "Staff No" and "Staff Name" respectively.
A6 - A205 is a list of Staff Numbers.
B6 - B205 is a list of Staff Names.
C6 - C205 is hidden (it is a repeat of the staff numbers automatically filled in for VLOOKUP reasons)
D6 - DS205 contain dates of when the training was completed.

The matrix works, althought it mat be tweaked to include other elements. The next page I want to create is a "REPORT".
This report links to the first page using VLOOKUP to generate results. The staff number is needed to identfy the staff members in the rare case of a name being the same. The report page uses VLOOKUP to offer a dropdown box of names from cells B6:B205, and automatically fills in the Staff Number from cells C6:C205 based on the results of the VLOOKUP.

I also want the report to list the training that the person has undertaken. So, there may well be 120 jobs on one matrix, but one operative may only be trainined in 5 of them. I want the report to list the training he has been given, follwed by the date it was completed.

I was unsure on how this would work with VLOOKUP and don't think it will, so I am unsure how I should tackle this problem.

So basically, I need to return the values of a complete row based on the VLOOKUP of the Staff Name (or number), and also include the column titles.

Does anyone have any solutions to this problem??

Regards,

Gaz

**Andy Pope** · 11-30-2010, 07:25 AM

Sounds to me as if the large matrix of employees by training is not the correct way to store the data. It is more of a report in itself.

I think you should store the data in a table with fields such as,
EmployeeID,EmployeeName,TrainningModule,DateCompleted

You can then use formula or pivot tables to produce individual employee reports or a complete matrix.