I have an excel sheet with in column A names and in the columns B to Z I have dates (D), numbers (N), text (X) or blanks. I also have a helper sheet that determines where what kind of data needs to be and I have conditional formatting for:
-Orange: cell should have data but is empty
-Red: cell has data but date is in the past
-Green: Cell has data, date is in the future or it is text



What I want to do is to make an IF formula that returns "Not Valid" if true and "Valid" if false. The formula needs to check for each cell in a range (for example B2 to Z2):
If it's a date: if it's in the past
if it is empty: if it should be empty or not

Now I can make this work if I use the formula on 1 cell but if I use it on a range then it doesn't work anymore. The formula I have now is:

=IF(OR(IF(AND(ISBLANK(B2),OR(Help!A11="X",Help!A11 ="N",Help!A11="D")),TRUE,FALSE),IF(Help!A11="X",FA LSE,IF(AND(Help!A11="N",ISNUMBER(B2/1)),FALSE,IF(AND(Help!A11="D",ISNUMBER(B2/1),TODAY()<B2),TRUE,FALSE)))),"Not Valid","Valid")

I built this using the formulas for the orange and red conditional formatting.
I think it doesn't work because the formula can't handle several dates at once or something like that.

Maybe it is possible to make a simple formula saying 'if in range A2:Z2 a cell is red or orange then return Not valid, otherwise Valid'?

Unfortunately I can't share a sample file and I can't use VBA either.


I hope someone understands what I mean and that they can help me!