Validation on multiple column

[Jomathr]

Good afternoon everyone,

I thought I knew how to do this but seems not, I am trying to validate a cell value to see if it's in a name ranged and check if the cell above is the offset of the named ranged.

here is an example of what I have:

On sheet1 I have the following:
Cell C6 contain a category chosen from a dropdown list
Cell C7 is where I need the validation and type a productID (too many products to use a dependant drop down list)

on Sheet2 I have the following:
Column B contain all the productID
Column Q contain all the catégorie

for the validation I have to check if it exist in Sheet2!Column B wich is a dynamic named range called LI_Product_No AND if the product category in column Q is equal to C6

Here is what I thought that would work in custom validation:

=AND(LI_Product_No,OFFSET(LI_Product_No,15,0) = 'sheet1'!C6)

Anyone have a clue on what is wrong with my formula or another one altogether to solve this?

Thank you for your time

 

[Jomathr]

I think my mind was too set in VBA,

figured it out using Excel thinking by replacing the validation by:

=AND(LI_Product_No,VLOOKUP(C7,'sheet2'!product_Table,16,FALSE)=C6)

in wich Product_table refers to the entire table (B2:Q51) in this case

 

[wigi]

Thank you for the update & feedback.