I have a list (Finished Goods)
with five columns
(C1 - FItemNumber,
C2 - FItemDescription,
C3 - Diameter,
C4 - Length,
C5 - rawGoodsItemNo).
I have another list (Raw Goods)
with 6 columns
(C1 - RItemNumber,
C2 - RItemDescription,
C3 - LowerDiameter,
C4 - UpperDiameter,
C5 - LowerLength,
C6 - UpperLength)
For finished goods list I need to find an item from raw goods list. To do this, the finished goods item's diameter is checked
with the LowerDiameter and UpperDiameter AND the length checked with the raw goods LowerLength and UpperLength, and if WITHIN
the range of BOTH then only is that raw goods item is selected and inserted into the fifth column (rawGoodsItemNo) of
Finished Goods list.
Finished Goods
--------------
C1 C2 C3 C4 C5
A ItemA 5 10
B ItemB 6.5 27
etc etc
Raw Goods
---------
C1 C2 C3 C4 C5 C6
r1 Itemr1 2 7 5 15
r2 Itemr2 15 22 35 40
etc etc
So in the above case the finished good item A will have raw goods item r1 in its 5th Column.
I would imagine this will need
a formula in column 5 of Finished Goods to check through the raw goods list. Which formula?
How is this possible? I have been trying to use VLookup but know its inappropriate for the task.
If this involves using access I am fine with that as well. Any ideas welcome. Thanks.
_________________
Amit Shah
This message was edited by amitshah on 2002-04-14 21:45
This message was edited by amitshah on 2002-04-14 21:52
This message was edited by amitshah on 2002-04-14 21:53
with five columns
(C1 - FItemNumber,
C2 - FItemDescription,
C3 - Diameter,
C4 - Length,
C5 - rawGoodsItemNo).
I have another list (Raw Goods)
with 6 columns
(C1 - RItemNumber,
C2 - RItemDescription,
C3 - LowerDiameter,
C4 - UpperDiameter,
C5 - LowerLength,
C6 - UpperLength)
For finished goods list I need to find an item from raw goods list. To do this, the finished goods item's diameter is checked
with the LowerDiameter and UpperDiameter AND the length checked with the raw goods LowerLength and UpperLength, and if WITHIN
the range of BOTH then only is that raw goods item is selected and inserted into the fifth column (rawGoodsItemNo) of
Finished Goods list.
Finished Goods
--------------
C1 C2 C3 C4 C5
A ItemA 5 10
B ItemB 6.5 27
etc etc
Raw Goods
---------
C1 C2 C3 C4 C5 C6
r1 Itemr1 2 7 5 15
r2 Itemr2 15 22 35 40
etc etc
So in the above case the finished good item A will have raw goods item r1 in its 5th Column.
I would imagine this will need
a formula in column 5 of Finished Goods to check through the raw goods list. Which formula?
How is this possible? I have been trying to use VLookup but know its inappropriate for the task.
If this involves using access I am fine with that as well. Any ideas welcome. Thanks.
_________________
Amit Shah
This message was edited by amitshah on 2002-04-14 21:45
This message was edited by amitshah on 2002-04-14 21:52
This message was edited by amitshah on 2002-04-14 21:53