Data Validation: Consider a value in another column

Dear Friends,

I would like to create a Data validation (offset) considering validation of another column.

For example, in my sheet1, data is:

Please Login or Register  to view this content.

In col B2:B1000 of sheet2, I wish to create data validation using the CODE column of sheet1 but only if "N' is there in "Yes or No" column.

Ofcourse, I need to apply OFFSET like formula to have dynamic data validation.

Request your kind help.

Dear Friends,

Please help me.

See if this take care your needs.

Dear Sir,

It's working. Any other simplified method to get the desired result?

Dear Sir,

It's working. Any other simplified method to get the desired result (i.e. without creating one more column)?

Dear Sir,

Request your kind help to get a single-point formula for the solution.

Change the named formula List to this and you'll get rid of those empty selections at the bottom of the drop down list:

=OFFSET(Φύλλο1!$C$2,0,0,COUNTIF(Φύλλο1!$C:$C,"?*"))

That's about as good as you're going to get it.