Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking data from one workbook into another | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
Linking multiple data into one worksheet. | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) |