Creating a report from data

G

GazNicki

Board Regular
Joined
Nov 30, 2010
Messages
79
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.

Does anyone have any solutions to this problem??

Regards,

Gaz
 
Last edited:
Sort by date Sort by votes
GazNicki,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste


Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.
 
Upvote 0
GazNicki,


Sample worksheets before the macro:


Excel Workbook
ABDEFGHIJKLM
1Training 1Training 2Training 3Training 4Training 5Training 6Training 7Training 8Training 9Training 10
2Document Ref:
3Document Version:
4Date Issued:29/08/8230/08/8231/08/8201/09/8202/09/9003/09/8204/09/8205/09/8206/09/8207/09/81
5Staff No.Staff Name
60001G Parkinson25/12/8225/12/8225/12/8225/12/8225/12/82
Matrix
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D61. / Formula is =D6=""Abc
D62. / Formula is =D6-D$4>360Abc
D63. / Formula is =D6Abc
D64. / Formula is =D6-D$4<364Abc
E61. / Formula is =D6=""Abc
E62. / Formula is =D6-D$4>360Abc
E63. / Formula is =D6Abc
E64. / Formula is =D6-D$4<364Abc
F61. / Formula is =D6=""Abc
F62. / Formula is =D6-D$4>360Abc
F63. / Formula is =D6Abc
F64. / Formula is =D6-D$4<364Abc
G61. / Formula is =D6=""Abc
G62. / Formula is =D6-D$4>360Abc
G63. / Formula is =D6Abc
G64. / Formula is =D6-D$4<364Abc
H61. / Formula is =D6=""Abc
H62. / Formula is =D6-D$4>360Abc
H63. / Formula is =D6Abc
H64. / Formula is =D6-D$4<364Abc
I61. / Formula is =D6=""Abc
I62. / Formula is =D6-D$4>360Abc
I63. / Formula is =D6Abc
I64. / Formula is =D6-D$4<364Abc
J61. / Formula is =D6=""Abc
J62. / Formula is =D6-D$4>360Abc
J63. / Formula is =D6Abc
J64. / Formula is =D6-D$4<364Abc
K61. / Formula is =D6=""Abc
K62. / Formula is =D6-D$4>360Abc
K63. / Formula is =D6Abc
K64. / Formula is =D6-D$4<364Abc
L61. / Formula is =D6=""Abc
L62. / Formula is =D6-D$4>360Abc
L63. / Formula is =D6Abc
L64. / Formula is =D6-D$4<364Abc
M61. / Formula is =D6=""Abc
M62. / Formula is =D6-D$4>360Abc
M63. / Formula is =D6Abc
M64. / Formula is =D6-D$4<364Abc





You can not see the graphic Staff Training Report


Excel Workbook
ABCDEFGHIJ
1
2
3
4
5
6
7
8
9Name:G ParkinsonEmployee No:0001
10
11
12
13TrainingDate
14
15
16
17
18
19
Staff Report





After running the CreateReport macro by pressing on the graphic Staff Training Report:


Excel Workbook
ABCDEFGHIJ
1
2
3
4
5
6
7
8
9Name:G ParkinsonEmployee No:0001
10
11
12
13TrainingDate
14Training 125/12/82
15Training 325/12/82
16Training 525/12/82
17Training 725/12/82
18Training 925/12/82
19
Staff Report





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel


Code: 
Option Explicit
Sub CreateReport()
' hiker95, 12/01/2010, ME512125
Dim wSR As Worksheet, wM As Worksheet
Dim Mrow As Long, NR As Long, LR As Long
Dim c As Range, firstaddress As String
Dim ColName As String
Set wSR = Worksheets("Staff Report")
Set wM = Worksheets("Matrix")
Mrow = 0
On Error Resume Next
Mrow = Application.Match(wSR.Range("C9"), Worksheets("Matrix").Columns(2), 0)
On Error GoTo 0
If Mrow = 0 Then
  MsgBox "The Name '" & wSR.Range("C9").Value & "' is not on worksheet Matrix - macro terminated!"
  Exit Sub
End If
Application.ScreenUpdating = False
LR = wSR.Cells(Rows.Count, 3).End(xlUp).Row
If LR >= 14 Then
  wSR.Range("C14:C" & LR).ClearContents
  wSR.Range("G14:G" & LR).ClearContents
End If
NR = 14
With wM.Rows(Mrow)
  Set c = .Find("*/*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      ColName = Replace(Cells(1, c.Column).Address(0, 0), 1, "")
      wSR.Range("C" & NR) = wM.Range(ColName & 1)
      With wSR.Range("G" & NR)
        .Value = c
        .NumberFormat = "dd/mm/yy;@"
      End With
      NR = NR + 1
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
wSR.Activate
Application.ScreenUpdating = True
End Sub



On worksheet Staff Report, right click on the graphic Staff Training Report, click on Assign Macro..., in the list find, and click on CreateReport, and then click on the OK button.


Then run the CreateReport macro by pressing on the graphic Staff Training Report:
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Highlighting Cells Using Selected Header Cell & Dynamic Changes
Replies
1
Views
511
DanteAmor
DanteAmor
M
  • Question Question
Dynamic link from Word to Excel...
Replies
8
Views
613
Matteo85
M
M
  • Question Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
1
Views
556
bobaol
B
E
  • Question Question
Find data from a range and then the oldest date from a row when matched
Replies
0
Views
274
-emma-
E
A
  • Question Question
Selecting data based on 2 criteria from another worksheet to create a singing in document.
Replies
1
Views
485
jdellasala
J

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top