Increment cell reference from other sheet

Question

I have an increment issue I can't figure out. 

I want to sort my list

So if a row has a value ="Y" then insert this reference form another sheet. 

=IF(W18="Y",'UPC Code file.txt'!$A$16)

I am filling UPC codes to a product, but only if it is a current product. 

How do I set this up so when I drag down the formula, it will jump to the next code, not to the next row reference (If I skip 22 product as they are no longer in inventory, I don't want to assign UPC to them). 

I appreciate any thoughts on this. 

Thanks.

Answer 1

So, if I understand you correctly, if W18 and W22 contain "Y", and W19:W21 contain "N" you want the formula on row 18 to deliver 'UPC Code file.txt'!A16 and you want the formula on row 19 to deliver 'UPC Code file.txt'!A17 ?

The ideas I have thought of will probably only work if the .txt file is open.  Is that OK?

I will suppose that the first row containing the formulas is row 2 and that the first UPC Code is in row 2 of the txt file.

The formula on row 2 would be

=IF(W2="Y",OFFSET('UPC Code file.txt'!$A$1,COUNTIF(W$2:W2,"Y"),0),"")

That formula can then be copied down