Record recording

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

 

[Roger Govier]

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

 

[Max]

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