Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Record recording
I need help in creating an automatic record form a file to an individual record. Example: You would make a work order for three different employees the first employees work order enter name, date, location, time, cost, and then this information would automatically be recorded to a permanent work sheet record specific to that employee. I have tried to manipulate VLOOKUP for this but to no avail. Any clue or help would be greatly appreciated. -- cdixon ------------------------------------------------------------------------ cdixon's Profile: http://www.excelforum.com/member.php...o&userid=28924 View this thread: http://www.excelforum.com/showthread...hreadid=486666 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Record recording
Hi
Rather than copying the data out to another sheet, why not just use Autofilter? Mark the column headings. DataFilterAutofilteruse the dropdown on Employees Name to bring up a list of just his/her work orders. Regards Roger Govier cdixon wrote: I need help in creating an automatic record form a file to an individual record. Example: You would make a work order for three different employees the first employees work order enter name, date, location, time, cost, and then this information would automatically be recorded to a permanent work sheet record specific to that employee. I have tried to manipulate VLOOKUP for this but to no avail. Any clue or help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Record recording
One play to automate it using non-array formulas ..
Sample construct at: http://cjoint.com/?lvcsEIj7mP AutoPlaceData_BySheetName__WorkOrder_By_Staff.xls Assume the master list is in sheet: Master in cols A to C, headers in row1, data from row2 down WorkOrd# AssignedTo WO_Desc 1111 Staff1 Descr1 1112 Staff3 Descr2 1113 Staff2 Descr3 1114 Staff4 Descr4 etc Using empty cols to the right of the data, say cols K onwards List the staff names in K1, L1 across: Staff1, Staff2, etc Put in K2: =IF($B2=K$1,ROW(),"") Copy K2 across to N2, fill down to say, N10 to cover the max expected source data range Click Insert Name Define Put under "Names in workbook:": WSN Put in the "Refers to:" box: =MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1") ))+1,32) Click OK (The above defines WSN as a name we can use to refer to the sheetname in formulas. It will auto-extract the sheetname implicitly. Technique came from a post by Harlan.) In a new sheet named: Staff1 With the same col headers in A1:C1, viz.: WorkOrd#, AssignedTo, WO_Desc Put in A2: =IF(ISERROR(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,M aster!$K$1:$IV$1,0)),ROWS( $A$1:A1))),"",INDEX(Master!A:A,MATCH(SMALL(OFFSET( Master!$J:$J,,MATCH(WSN,Ma ster!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(Master!$ J:$J,,MATCH(WSN,Master!$K$ 1:$IV$1,0)),0))) Copy A2 across to C2, fill down to C10 (cover the same range size as was done in "Master"' cols K, etc) Cols A to C will auto-return only the lines for: Staff1 from "Master", with all results neatly bunched at the top Now, just make a copy of the sheet: Staff1, rename it as: Staff2, and you'd get the extracted results for Staff2. Repeat the sheet copy & rename as required to obtain the specifics for Staff3, Staff4, etc. Records updated in "Master' will auto-reflect in each staffs' sheet Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "cdixon" wrote in message ... I need help in creating an automatic record form a file to an individual record. Example: You would make a work order for three different employees the first employees work order enter name, date, location, time, cost, and then this information would automatically be recorded to a permanent work sheet record specific to that employee. I have tried to manipulate VLOOKUP for this but to no avail. Any clue or help would be greatly appreciated. -- cdixon ------------------------------------------------------------------------ cdixon's Profile: http://www.excelforum.com/member.php...o&userid=28924 View this thread: http://www.excelforum.com/showthread...hreadid=486666 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Record data on two lines | Excel Discussion (Misc queries) | |||
Record information is on two lines | Excel Discussion (Misc queries) | |||
Can't go to 'Last Record' in one step in a Word doc. linked To Exc | Excel Discussion (Misc queries) | |||
Find Record | Excel Discussion (Misc queries) | |||
recording the date when record was entered in cell in Excel | Excel Worksheet Functions |