+ Reply to Thread
Results 1 to 3 of 3

linking data

  1. #1
    kevhatch
    Guest

    linking data

    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

  2. #2
    Bill Ridgeway
    Guest

    Re: linking data

    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

    "kevhatch" <kevhatch@discussions.microsoft.com> wrote in message
    news:F5F452BD-58AF-4022-845B-942911B27929@microsoft.com...
    > 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




  3. #3
    Norman Jones
    Guest

    Re: linking data

    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

    ---
    Regards,
    Norman


    "kevhatch" <kevhatch@discussions.microsoft.com> wrote in message
    news:F5F452BD-58AF-4022-845B-942911B27929@microsoft.com...
    > 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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1