Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup??
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... -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=476166 |
#2
|
|||
|
|||
Given that sheet1 has a table of values, sheet2 can have a similar table, but with formulae. Every cell in sheet2 corresponds to the same 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". For 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 problem is you'll find loads of empty rows. To shorten this, highlight the 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 the table. -- Faz ------------------------------------------------------------------------ Faz's Profile: http://www.excelforum.com/member.php...o&userid=27830 View this thread: http://www.excelforum.com/showthread...hreadid=476166 |
#3
|
|||
|
|||
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. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "cjjoo" wrote in message ... 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... -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=476166 |
#4
|
|||
|
|||
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 __________________________ |
#5
|
|||
|
|||
vlookup??
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. -- cjjoo ------------------------------------------------------------------------ cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916 View this thread: http://www.excelforum.com/showthread...hreadid=476166 |
#6
|
|||
|
|||
vlookup??
Hi
"cjjoo" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |