Data Validation with Two Cells Referenced

[sjcocks]

Hi guys,

Hopefully quite a quick question here. I'm trying to add data validation to a cell, based on two criteria:
1) The value entered in the cell must be a number
2) The cell one to the left of it must be blank

In the custom formula input of the Data Validation box for cell B1, I have:

=AND(ISNUMBER(B1),A1="")

This works fine to detect whether A1 is blank (Rule 2), but allows any value to be input in cell B1 (defying Rule 1) when cell A1 is blank. I presume it has something to do with checking the status of two cells; as if I change the formula to say:

=AND(ISNUMBER(B1),1=1)

It is able to detect whether the value put in B1 is a number (Rule 1), but obviously it no longer checks whether A1 is blank (Rule 2).

I can't see anything online outlining limitations of data validation that would explain this behaviour so it would be great if someone could help me work out what's going on here and get something working!

Thanks a lot.

 

[rogerlloyd]

Perhaps an alternative method might work for you.

Put a custom data validation in for cell B1: =A1="" and then in cell C1 enter a formula of =B1*1. No entry will be allowed in B1 if A1 is not blank and the result in cell C1 will alert you with an error message if text is entered in B1. What do you think?

 

[sjcocks]

Hi Roger,

Thanks a lot for your response. Since posting, I managed to get it working as desired if I put the formula:

=AND(ISNUMBER(B1),A1="")

in cell C1 and then put "=C1" in the data validation formula for cell B1. I am quite confused as to why this works but the other doesn't.

It would be much better for this project if I can get the data validation working without having to use up an extra column, so it would be great if anyone would be able to suggest a way of doing this, or at least explain why the method I am trying isn't working.

Thanks