linking data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a worksheet that I enter details of worksheets handed in by employees
i.e. in col1 name, in col2 date and in col3 if worksheets not handed in I
enter L,H or S (Late,Holiday or Sick).

What I am after is to list the names of employees on a new sheet if they
have L,H or S against their name and also to show against their name the
reason (Late,Holiday or Sick rather than L,H or S).
Hope this makes sense.
Thx in advance
Kev
 
Have you looked at the possibility of using a pivot table? In essence this
gathers required information into a table. Have a look at <Data><Pivot
table and pivot chart report ...>

Basically you drag the headings into the relative parts of the report.
However, in practice you may need to have a few goes before getting it just
right.

Regards.

Bill Ridgeway
Computer Solutions
 
Hi Kev,

You could use the Advanced Filter feature to extract the Late, Holiday and
Sick data to another sheet.

If you are not familiar with the Advanced Filter, see Debra Dalgleish's
tutorial at:
http://www.contextures.com/xladvfilter01.html

If you wish to automate this, you could turn on the macro recorder while you
perform the steps manually. This will provide you with base code which can
be edited for deneral application. If you experience any problems with such
editing, post back with specifics.

If, alternatively, you wish the record to be maintained dynamically, try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)

Dim destSH As Worksheet
Dim destCell As Range
Dim arr As Variant

If Target.Count > 1 Then Exit Sub

Set destSH = ThisWorkbook.Sheets("Record") '<<==== CHANGE
Set destCell = destSH.Cells(Rows.Count, "A").End(xlUp)(2)

arr = Array("L", "H", "S")

If Not Intersect(Target, Columns(3)) Is Nothing Then
With Target
If Not IsError(Application.Match(UCase(.Value), arr, 0)) Then
.Offset(0, -2).Resize(1, 3).Copy Destination:=destCell
End If
End With
End If

End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

If you are not familiar with macros, you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top