Lookup woes

[wmjenner]

I am trying to create a formula using index, match, and/or offset but I just can't get it to work. The lookup value is a single cell in one worksheet, the data are in another. The problem is that I want to look up a production work order number and then go down and over for specific values.

Here's a brief example: Say the work order number is 123.

In the second worksheet I have:

Work Order No. 123

Production Steps---------Machine------------Due Date
Generation--------------------------GP1---------------------2/28/07
Grinding-----------------------------GP2---------------------2/29/07
Polishing-----------------------------GP3---------------------4/1/07
QA----------------------------------GP4----------------------4/5/07

Work Order No. 456

Production Steps---------Machine------------Due Date
Generation--------------------------GP3---------------------3/10/07
Grinding-----------------------------GP5---------------------3/15/07
Polishing-----------------------------GP6---------------------4/5/07
QA----------------------------------GP2----------------------4/15/07
Work Order No. 789

Work Order No. 789

Production Steps---------Machine------------Due Date
Generation--------------------------GP2---------------------3/9/07
Grinding-----------------------------GP12---------------------3/29/07
Polishing-----------------------------GP4---------------------4/1/07
QA----------------------------------GP6----------------------4/5/07

I want to create a work order traveler that is set up with the same items listed above. I want to be able to look up work order no. 123 and then pull in the data that goes with that particular work order. The search should go through ALL of the work orders listed (I've shown three here - there are dozens) and return the data for the work order that I've entered on the traveler.

I've checked out a lot of online ideas but none of them quite fit. Any help will be greatly appreciated!

[MSP77079]

You know that you posted this under FUNCTIONS right? This is a piece of cake with a VB macro.

You have got Match / Index working well enough to find the starting row of the match you are looking for, right? Well, you use that and then return the offset range you want. Couldn't be easier. But, it takes a macro.

Now ... I have written those famous words before and had some brilliant person write back and tell us about some excellent bit of Excel of which I was ignorant. (Bryan did that just a day or two ago, as I recall). So, don't give up hope.

Likewise, don't hold your breath.

If you want a macro, upload a skeleton of your workbook (dummy data is fine). Show me where you want the return to go and where to put the button. (Gee ... would you like a drop-down box so the user could select a work order from a list that we can populate every time the workbook opens and/or every time a new workorder is added to the sheet?).

[wmjenner]

OK - I'm attaching a sample. A macro would be great. I want to pull the data into the worksheet "Traveler" from the worksheet "Source". I would type the work order number into C3 of Traveler and it would pull the corresponding data for that worksheet from Source.

Thanks for offering to help!

[MSP77079]

Hi Bill, the workbook is an excellent example. I can definitely work with this and have a working model within a day or so. Two issues I have found (so far) that I will need your help with ...

Task lists do not match between the Traveler sheet and the Source sheet; nor among the 3 examples on the Source sheet. Examples, "Generate curve" is not the same as "Curve Generation"; "Grinding/Lapping" is not the same as "Grind". Two options:
1. use example shown on Traveler sheet, use a bit of VB logic to attempt to match task lists as well as possible. I can make a first approximation at this, but you will need to get involved heavily in how this logic works.
2. more like suggested in your first posting ... namely, pull in what IS on the Source sheet.

The advantage of #1 is it does show very clearly what is NOT required for a given job. The disadvantage of #1 is that the logic is likely to need a lot of tweaking over time. People could forget how the logic works, users could become frustrated and lose faith in the workbook.

Where does "due date" come from? I do not see this on the Source sheet.

I can start without the answers to these 2 questions. If I spot any other issues, will let you know.

[MSP77079]

The example you gave, work orders are always numbers only. Is this a true example? Or will work orders sometimes be a combination of letters and numbers?

The real question is ... how will I know when I have found a work order? Will it because:
a. the row above it is blank?
b. it has "Cust." in column B?
c. it is just above the word "Fabrication"?
d. it is always a number with no letters (as in the example)

[wmjenner]

To your first two questions:

1. The names can be changed to match exactly - they're only different because I've been working on this for a while and haven't been very consistent. But, in fact, they are the same. I was trying to pull by offsets so the name wouldn't matter but this is by no means a requirement. I have no problem getting involved with the logic - in fact I'd LIKE to be so I can tweak it later. That's about the extent of my VB knowledge - tweaking what others have done.

2. Again, whichever works best.

Re: Work order numbers. They will usually be numbers but let's assume they will be a mix just in case. I would say that either "b" or "c" would work to identify the work order. If it would simplify things for you, I could start every work order number with WO in the same cell ("WO 123"). That way you could just look for fields that start with WO.

The due date on the traveler should be the finish date shown in the source worksheet (also pulled in). I converted all the formulas to values in my sample to meet the 100kb max.

Finally, don't worry about people losing faith or forgetting how the logic works. I will be the main user and I won't do either.

Let me know of any other question! And thanks!

[MSP77079]

How it works ...
There is a combo box hiding cells C3:D3 on Traveler sheet. This box is populated with list of all work order numbers. More on when and how that list is updated further below.

You pick a workorder from the list and press "Get Results" button. This calls a routine that fills the table below.

HOW THE COMBO BOX LIST IS CREATED
If you want to follow along, select Tools >> Macro >> Macros, select the routine named "generateWO_List" and click Edit. This will open the VB Editor with the code for the generate WO list macro showing. This is the code which keeps the list of work orders up-to-date.

This code defines the 3 worksheets:
+ Traveler
+ Source
+ References (this sheet is hidden, but having it visible or hidden is your option if you like)
If you change the names of these worksheets, you will need to change the code. There are other ways to write code that would allow this to still work even if you change the sheet names. But, I find that harder to explain to people; so, I did it this way.

The first thing the code does after defining the 3 sheets is to "unhook" the combo box from cell C3. This way, we can "remember" what value the user was looking at last.

Next, it clears the cells on the References sheet, then rebuilds the list of work orders. Not wanting to trust users (even if that means only you) too much, the code will "recognize" that it has reached a new work order if any of the following are true:
The cell in Column B says "Cust."
The cell in column A on the next row says "Fabrication"
The cell in column B on the next row says "Machine"
The cell in column C on the next row says "Hours"
The cell in column D on the next row says "Start"
The cell in column E on the next row says "Finish"

Using, Select Case the routine stops checking when it reaches the first "True" case.

When it finds a work order, it records the WO number (and the row it was found on) in the next blank row of the References sheet. It determines the last row with data for that WO using the "CurrentRegion" property. If you are not familiar with this, you can see how it works by (in the normal Excel view) selecting a cell, pressing F5 (or use Edit >> Go To), selecting "Special", then selecting "Current Region", and clicking "OK". If you play around with this enough, you will come to understand that this finds a data table, and is not "fooled" by an empty cell here or there. It is looking for an entire rows and columns with nothing in it.

The last row is also recorded on the References sheet.

After searching the entire Source sheet (which happens pretty fast; VB can read from Excel about 50,000 times faster than it can write to Excel), we use the Insert >> Name >> Create feature to create named ranges for WO_Number, FirstRow, and LastRow.

Then, we tie the range named "WO_Number" to the list in the ComboBox on the Traveler sheet. And finally, attempt to set the value in the ComboBox back to the value it had to start with. I say "attempt to" because if you change a WO number on the Sources sheet, and that happened to be the last WO you were viewing on the Traveler sheet, we can't set the ComboBox back to that value. For reasons I will explain next.

OK, that's what the macro does when it runs. But, how does it know when to run? I have set it up to run whenever the Workbook is first opened, and again any time that you LEAVE the Source sheet. So, you can edit the Source sheet to your heart's content, but when ever you LEAVE the Source sheet, VB will quietly go back and re-create the list of Work Orders.

The code that calls the "generateWO_List" macro when you leave the Source sheet can be found on the code window of the Sheet2 (Source) object. The easiest way to find this code is to start in Excel; select the tab for the Source sheet; right-click, and select "View Code" from the context menu. Then, find where the cursor is flashing.

The code that calls the "generateWO_List" macro when the workbook opens can be found on the code window of the ThisWorkbook object. If you can't find it (and want to), let me know. I'll be happy to explain.

HOW THE WO INFO IS PULLED IN
To pull the WO information, you click the "Get Results" button. This runs the CommandButton1_Click routine, found on the Sheet2 (Source) object's code window. That routine reads which item was selected (by its index, or position, not its displayed value) from the ComboBox and passes this index/position to a VB routine that named "getWorkOrderDetails". The routine that does the work of getting the Work Order Details is found in the same code window (Module1) as, and just below, the "generateWO_List" macro.

After defining the 3 worksheet, the routine gets reference to the selected work order by translating the index/position in the ComboBox' list to a row number on the References sheet, using the Named range "WO_Number" to do the translation. Once we have the correct row number on the References sheet, this tells us the first and last rows for this Work Order's information on the Source sheet.

The rest of the routine is logic to tell us which info we want and where we want to put it.

This is probably the part you will want to mess around with, because I had to make a few guesses about what you really want to see.

Hopefully I have explained enough to make that relatively painless for you. But, if you have any questions, do not hesitate to ask.

[wmjenner]

MSP - you're amazing! It's exactly what I was looking for. Now I'll spend the next few hours/days/weeks poring over the code and your comments to see if I can actually grasp it.

Many thanks for taking the time to both do it AND explain it! I really appreciate it.