data validation in a cell in excel

[bkgashok]

hi friends

i wish to validate the cell B3 value as below

1. the cell B2 should not be blank
2. the values in B3 should be validated with a list located in another sheet (for example the list name is "PRODUCTS"

Pls any one help me to incorporate both the validations

thanks and regards

ashok

[6StringJazzer]

We need some more information.

For validating B2, the cell will start out as blank. The user may never touch it, and it will stay blank. Therefore Data Validation may not work, since it only evaluates the cell when it changes. At what point do you want to evaluate it to ensure that it is not blank?

Is "PRODUCTS" a named range? If so then you can simply use Data Validation with a Dropdown List, and specify =PRODUCTS as the list.

[bkgashok]

thank you 6StringJazzer for your kind reply

this is for writing into the column B. for B2 the validation would be only the named range products. it is simple, =PRODUCTS as the list will do.
but from B3,B4 and so on, there should not be a blank in between. for this i would like to add an other criteria that the cell above the current cell is not blank

[6StringJazzer]

OK, I was not clear on your complete requirements.

You cannot have a dropdown list and combine other validation criteria with it in the same cell. You can, however, ensure that what the user types in is a member of the list and combine that with your other requirement.

You would do data validation with a custom formula:

Please Login or Register  to view this content.

Uncheck the "Ignore Blanks" checkbox.