On Fri, 14 Oct 2005 02:30:16 -0500, cjjoo
wrote:


this is my problem:


i got 30 worksheets in a workbook and they are named 1-31( according to
the
day of that month)

the format of each worksheet is the same and they look like this:

unique id vehicle num date in date out status(can input either "ok"
or "not

ok" )

so in sheet 1 ,(day 1) two vehicles( no x123 and y456) comes into the
workshop.

x123 is repaired and the status will be key in as "ok"
y456 is not repaired and the staus will be key in as "not ok"

now, in day two(sheet 2), i want to return the vehicle no, the unique
id of y456

that is still in the workshop automatically . Is that possible?

The unqiue id comes from the concatenuate of the vehicle no and the
date in

i tried to use vlook up but the results are not desired.

pls advise...

Can I respectfully suggest that this may not be the best way of
arranging your workbook. It seems like you're trying to identify which
jobs are not complete and still in the workshop today.

Individual sheets for every day of the month are not necessary. If you
hold all the vehicle records in a database on say sheet 1, with date
in, date out and status, then you have all the information you need to
deduce the outstanding jobs in the workshop using say sheet 2.

I'm not sure you necessarily need the unique vehicle ID either.

However try the following. Sheet1 is your database. Put column
headings as follows. in say A1:E1

unique ID, Vehicle, DateIn , DateOut, Status

Now give a name, say "MyData" to A1:E1000

Now on sheet 2 put "Status" in A1 and "<OK" in A2 - both without the
double quotes.

Name the range A1:A2 "MyCrit"

Put the field names
Vehicle, DateIn, DateOut, Status in A5:D5 and name this range
"MyDataOut"

Now do an Advanced Filter and select the "Copy to another location"
option, in the List range box enter "MyData", in the CriteriaRange box
enter "MyCrit" and in the Copy to box enter "MyDataOut".

Any jobs on sheet 1 which don't have "OK" entered in Column E will now
be listed on sheet 2. You could improve this by attaching the
following Macro to a button.

Sub ExtractIncompleteJobs()

Range("Mydata").AdvancedFilter xlFilterCopy, _
criteriarange:=Range("Mycrit"), _
copytorange:=Range("Mydataout")

End Sub

Arguably you might not even need the Status marker if the criteria for
selecting incomplete jobs is that there is no Date Out recorded. In
whcih case you'd need to alter the criteria.

Just continue to add all jobs to Sheet1 one, and occasionally - say
every month delete all the ones you no longer wish to keep recorded.

HTH.







__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________