Record recording

  • Thread starter Thread starter cdixon
  • Start date Start date
C

cdixon

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.
 
Hi

Rather than copying the data out to another sheet, why not just use Autofilter?
Mark the column headings.
Data>Filter>Autofilter>use the dropdown on Employees Name to bring up a list
of just his/her work orders.

Regards

Roger Govier
 
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,Master!$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 ..
 
Back
Top