lookup

[Guest]

Hi
I have multiple columns containing different values, p/n, description, price
qty...

p/n desc price A B C D
23 valve 1.00 1 1 1
32 pump 2.50 1 1 2
3 plc 6.75 1 1 1
45sd sw 9.00 1 1

Where A,B,C,D are the complete units, the numbers below are the total of
each item on the left in the unit. (ie. P/N A has 1 valve and 1 plc)

I need to be able to ref a part number and list ONLY every item on that unit.

 

[Guest]

E is a count of A thru D, if E = 4 then completed.
HTH,

 

[Guest]

Gary

Thank you for the reply, but I think I was very vague in my description.

I want to be able to type "A" in a field, then below that have some
rows/fields populate with the items used in the "A" machine.

ie: Machine number = "A"

p/n desc price qty
23 valve 1.00 1
3 plc 6.75 1

Thank you, I hope this illustrates better what has me baffled.

 

[Herbert Seidenberg]

Arrange your data and headers as shown:
p_n desc price Au Bu Cu Du
23 valve 1.00 1 1 0 1
32 pump 2.50 0 1 1 2
3 plc 6.75 1 1 1 0
45sd sw 9.00 0 0 1 1

select
Du
p_n desc price qty rsel set2
32 pump 2.50 2 2 1
23 valve 1.00 1 1 2
45sd sw 9.00 1 4 3
4

Select the data under p_n, desc and price (12 cells) in the first
array and
Insert > Name > Define array1
Select the data under Au, Bu, Cu, Du (16 cells) and name it array2
Select the Au...Du header and its data (20 cells) and
Insert > Name > Create > Top Row
Repeat for the 2 cells (select, Du), the 5 cells (rsel,2,1,4,blank) and
(set2,1,2,3,4)
Extend the series of numbers of set2 to equal the number of parts (n).
Create an array the size of array2 and fill it with
=(RAND()-0.5)/100+1
Copy > Paste > Paste Special > Value, Multiply to array2.
Redo after updates.
Enter this formula into rsel and copy down n times:
=IF(set2>COUNTIF(INDIRECT(select),">0"),"",
MATCH(LARGE(INDIRECT(select),set2),INDIRECT(select),0))
In the results array, under p_n, desc, price (12 cells) enter
=IF(rsel="","",INDEX(array1,rsel,))
Under qty, enter and copy down n times
=IF(rsel="","",ROUND(INDEX(INDIRECT(select),rsel),0))
Under select, change Du to another unit as you please.