Data validation: If previous column is having a value, then return FALSE else TRUE

Dear Friends,

I am applying data validation in my workbook. The data in ColS consist of:

ColS
(Row2) X
(Row3) Y
(Row4) Y
(Row5) Y
(Row6) X

In ColT, I am to apply data validation: i.e. If in concerned row of ColS is having "X" value, then I am to show an error message. Else, values from A2:A5 should be shown in the as list and these should be accepted in ColT.

Thanks in advance.

acsishere.

I already tried with this kind of formula. It doesn't work.

I have data in sheet1!A2:A10 (range named as ReadingMat). The data is:
A2-PHYSICS
A3-CHEMISTRY
A4-BIOLOGY
A5-ZOOLOGY ...and so on.

In sheet2, I am to do dataentry. ColS is having data like B (for Books); J (for Journals); T (for Thesis); M (for Maps).

When I am to enter data in ColT, first I am to check ColS (of conerned row) whether "M" is there. If so, then I am to say that "Maps are not Classified". Else, I am to accept the values of ReadingMat (named range).

Thanks in advance.

acsishere.

I have attached a sample file & explained in detail, for your kind ref.

Please give me a solution.

Thanks in advance.

acsishere.

Now, the file is attached.

Sorry for the inconvenience.

acsishere.

Is it not possible? Any other alternatives?

acsishere.

Thanks for your valuable time. If you say that it is not possible with formula, then I will try to change logical design of my worksheet.

Thanks a lot for your guidance.

acsishere.

Here is a possible solution using the data validation in combination with VBA. The VBA code is connected to the spreadsheet (Right click on Sheet2 tab and "View code"). What it basically does is erases your entry if there is an "S" in column 6 (F). If not, your data validation works normally.

The code is as follows

Please Login or Register  to view this content.

ChemistB

Thanks a lot for your code. It works very nicely.

Again, Thanks! Thanks! Thanks!

acsishere.