Lookup and offset

  • Thread starter Thread starter wmjenner
  • Start date Start date
W

wmjenner

Oops. I guess you can't hit "enter" like you're typing.

009475 009475 009478 009478
2/16/04 8749 2/16/04 8750
3/8/04 8680 3/8/04 8681
3/8/04 8830 3/8/04 8963
3/22/04 8706 3/8/04 8831
3/22/04 8707


Row "A" is part numbers of products that we manufacture. A2 is th
date a production work order has been scheduled and B2 is the wor
order number generated by the ERP system and to which we will build th
quantity called for. C1 is the next part number, under which are al
open work orders associated with that part number. There may be 100
part numbers with open work orders going across the spreadsheet.

In a separate spreadsheet I want to look up from the above table - b
part number AND date and return both the date and any open work order
for each part number for the specific date. This forms the productio
schedule. It should look something like this:

2/16/04 2/23/04 3/1/04
3/8/04

Part No. 9475 8479
8680, 8830
Part No. 9478 8750
8681, 8963
Part No. XXXX

I've tried doing hlookups with an offset to pull the work order numbe
in but I can't seem to get it to work. It doesn't seem tha
complicated but I guess I'm not quite grasping the offset syntax.
simply want it to lookup the part number, look up the date below it
and then look up the work order number that is one column to the righ
of that date. I am pulling each date into a separate cell in th
target spreadsheet, so the lookup itself is easy. But why is th
offset giving me such a hard time (#Value, #N/A, - you name it).

Thanks for any help!

Bil
 
You may not like this idea, but I think I'd rearrange my data.

Column A would contain the part number
B would contain the workorder number
C would contain the production date

then I think you could use Data|pivottable to summarize your data.

But I think I'd do a little experimentation first. Do a little of the work
(converting your data) and see if the pivottable works before you spend too much
time on it.

In most cases, once you have your data laid out nicely, you can do lots of stuff
to it.
 
Thanks Dave. I've played around quite a bit with pivot tables - in fac
I use one for the actual production quantities. But the work orde
numbers are a bit trickier. And I can't rearrange the data because (a
it is already being pulled from a table similar to what you describ
and (b) I am using Alan Beban's wonderful vlookups formula to do so an
the data is stacked like in my example. A couple of formulas have com
up on the excel newsgroup that seem to solve the problem. Thanks t
all for the great tips
 
Back
Top