Hi guys,

I've been trying to put together a macro for a while to do this, with no success (details below). I consider myself a pretty decent Excel user; I can't write pure VBA code, but using Record, a few Google searches, and a little wit I've been able to solve a fair amount of macro issues, except this one.

I want to write a macro for a spreadsheet used for managing orders. The orders are each one line, and call out a certain quantity of a 6 digit item number; there are some orders that call out the same item number.

ex.
Order Item Qty
1 111111 10
2 222222 10
3 111111 5

On a separate tab, I have an inventory sheet. Each line represents a "lot" of an item number, with a corresponding quantity. There are multiple lots of the same item number.

ex.
Lot Item Qty
AA 111111 5
BB 222222 5
CC 222222 10
DD 111111 10

What I'm aiming to do is set up a macro that will go to the inventory sheet and create a named list that consists of every lot for a given item number, and have that done for every item number. From there, I want to have a cell on the orders sheet that uses Data Validation to limit the cell's value to a drop down of the lots that match that row's item number. I would want the end result to look something like this.

ex.
Order Item Qty Lot Lot Qty
1 111111 10 DD 10
2 222222 10 CC 10
3 111111 5 AA 5

Keep in mind there's a couple hundred lines of orders, and a few thousand lines worth of inventory, so you can imagine how long this process takes manually, or even with V-Lookups (plus then you get overlapping lots). I can handle the v-lookups associated with the quantities, I just really need help with a macro that will create a list at every change in item number, and an a data valiation who's list name can be based on a cell's value, which fills down a whole column. Any help at all would be greatly appreciated; thanks guys.

-Mike