Selected date from a list

[Fritz]

I have a list of part numbers on one Worksheet in a Workbook. There are 110
rows of data with 3 columns of information associated with each part number
(qty, pt number, price) I want to have another worksheet in my workbook
automatically populated with all 3 columns of information only for the items
where a user has indicated a selected qty for each part number...how do I
accomplish this?

 

[Bernie Deitrick]

Fritz,

The solution will depend on whether your part number is in the first or second column of the other
sheet.

IF it is in the FIRST column, then use
=IF(qty>0,VLOOKUP(partnumber,Address of 3 columns of Table in $$ form, 2 for quantity or 3 for
price, False), "")

It will look like
=IF(B2>0,VLOOKUP(A2, Sheet1!$A$1:$C$110, 3, False), "")

If it is in the SECOND column, then use

=IF(qty>0,INDEX(FirstColumn, MATCH(partnumber,second col of Table in $$ form, False),"")
It will look like

=IF(B2>0 ,INDEX(Sheet1!$A:$A, MATCH(A2, Sheet1!$B:$B, False),"")

HTH,
Bernie
MS Excel MVP