which formula should I use?

[birdiegirl]

Hi, I know just enough about Excel to make me dangerous and to make my boss think I can do anything .
I have a spreadsheet with 44 sheets, it is for inventory going to our conference. out = inventory being sent to conference and in = inv coming back from the conference. we have a column that is for Y or N saying if the box is going to return. I would like to have the inv on the OUT sheet be listed on the IN sheet only if it has a Y in column D.
I started out using IF, but kept getting a #NAME error. so I next used VLOOKUP to pull the information over from OUT to IN, but this pulls both Y and N's, I am not sure how to word the formula to only pull Y information.
the VLOOKUP formula I used is =VLOOKUP($A3,OUT!$A:$J,3,0). this is on a second version of this file, not the one I am uploading.
I figured the formula was overkill since I could just tell the file to =OUT!B3 and get the same result as the VLOOKUP formula, IE everything from the OUT sheet.
I would just tell my boss to filter the IN sheet to show only Y items, but his concern is that if he mistakenly puts a wrong number, it will still pull the information from the IN tab even though he cannot see it there and he may not notice. He would like for the page to tell him it does not like the wrong number by not putting any information for that number. is this possible? or will I need to tell my boss not to put a wrong number
thanks for looking and any help will be appreciated

Birdiegirl

 

[bosco_yip]

Try…….

1] In "OUT sheet" helper L3, formula copy down to L400 :

=IF(D3="y",MAX(L$2:L2)+1,"")

2] In "IN sheet" A3, formula copy across to J3 and all copy down :

=IFERROR(INDEX(OUT!A$3:A$400,MATCH(ROWS($1:1),OUT!$L$3:$L$400,0)),"")

Regards
Bosco