![]() |
#1
|
|||
|
|||
![]()
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! |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can we buy Access separately? | eeaakkat | Office | 2 | 04-04-2017 08:28 PM |
![]() |
John Witmer | Word | 2 | 02-04-2017 02:35 PM |
Spell check on leaving a cell | WPeacock | Excel Programming | 0 | 02-21-2016 06:05 PM |
![]() |
robbinskelly7 | Excel | 1 | 02-09-2015 07:05 PM |
![]() |
etodem | Excel | 2 | 10-23-2010 07:33 PM |