vlookup??

[cjjoo]

this is my problem:


i got 30 worksheets in a workbook and they are named 1-31( according t
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 th
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 uniqu
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 th
date in

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

pls advise..

 

[Faz]

Given that sheet1 has a table of values, sheet2 can have a simila
table, but with formulae. Every cell in sheet2 corresponds to the sam
cell in sheet1, so sheet2 D6 holds the formula relating to sheet1 D6
As an example, column C is the column stating "ok" or "not ok". Fo
sheet2 A1, use this:

=IF(Sheet1!$C1="not ok",Sheet1!A1,"")
Drag this to the bottom of the table.

Sheet2 now only shows the rows with "not ok" in column C. The proble
is you'll find loads of empty rows. To shorten this, highlight th
entire table and sort it by any column you wish in descending order
this will group all "ok" (invisible rows) together at the bottom of th
table

 

[Arvi Laanemets]

Hi

It'll be best, to enter all data into single sheet - you even don't almost
need to redesign anything. Maybe only instead of Status implement a Repaired
column, where the date when vehicle was repaired is entered. I.e. something
like
sheet Workshop: EntryID, Vehicle, DateIn, DateRepaired, DateOut, ...

Now you can relatively easily design a couple of report sheets, where on any
of them you determine some criteria, and a report based on those criteria,
and on data from sheet Workshoop, is displayed. Like:
- The list of vehicles, currently in workshop;
- The list of vehicles, being in workshop on selected date;
- The list of vehicles, taken into workshop on selected date;
- The list of vehicles, which were in workshop during determined time
interval (p.e. during selected month or year);
- The list of repairements for a determined vehicle;
etc.

On workshop sheet, I advice you to use FreezePanes and Autofilter features.
It allows easily to find wanted entry, or to hide old entries. For some
cases, Autofilter can be a replacement for a report.

You can delete old entries (deleting whole row) periodically, or, when you
want to preserve them, you can keep the workbook for year or month (it
depends on how much entries you'll have - a couple of thousands is OK,
~>10000 will cause the workbook to be too slow). When preserving old data,
you rename old workbook with a name indicating period (like
Workshop2005.xls), and clear all old data from original one (Workshop.xls).
You have to consider, how to handle entries, which were taken in in one
period, and were taken out in another - or you have double entries for them
in both workbooks, or you mark them as taken out on last day in archived
workbook, and as taken in on 1st day in current one.

 

[Richard Buttrey]

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 A55 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
__________________________

 

[cjjoo]

hi guys , appreciate the feedback u all gave. but the thing is that in
column E to I

there are other headings like : problems identified, action taken ,

action_by_which mechanic , time_in_ by mechnic, time_out_by_mechanic

then there is another sheet(named summary) where it is the
consolidation of all

the 30 sheets. So in the summary , i can see the history of all the
unique job

sheet Id and the repairs that has been done by which mechanic.

I dun noe if this is the best way , please advise.

 

[Arvi Laanemets]

Hi

hi guys , appreciate the feedback u all gave. but the thing is that in
column E to I

there are other headings like : problems identified, action taken ,

action_by_which mechanic , time_in_ by mechnic, time_out_by_mechanic

And so what? You can as many additional columns on worksop sheet, as you
want.

then there is another sheet(named summary) where it is the
consolidation of all

the 30 sheets. So in the summary , i can see the history of all the
unique job

The best way to get a history for a job is to use autofilter on workshop
sheet.


Arvi Laanemets