#1




Check for value in each cell separately
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! 
#2




How about using data validation to preclude incorrect entry of data?

#3




I do use data validation for some cells but the valid/not valid part is for easy checking if someone has all the required certificates to (be allowed to) do their job. I could check it manually for someone and change it manually to valid or not valid but after a while some certificates will expire so it would be great if it changed to not valid automatically in such cases. Besides checking manually would be very annoying because there are over a hundred columns.

#4




Unfortunately, there aren't any cell formulas that can check for fill color. That requires either data filters or VBA and neither would be suitable in your case as I see it.
There might be a way to do what you want only it would take a bit of work and will make your workbook twice as big. What you could do is have a corresponding helper column check the validity of each of those cells and return a value, say 0=valid and 1=invalid. These cells could be hidden to make the sheet more visually manageable. Then you would just have one formula that would add up all these cells and display either a valid/invalid or even just the sum (to indicate how many invalids). It's kind of a kludge, I know, but Excel don't care. :) 
#5




Hmm I'd prefer to do it without helper columns but I'll give it a try tomorrow

Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Can we buy Access separately?  eeaakkat  Office  2  04042017 08:28 PM 
How to add a title page (created separately) to a long document with headings and a table of content  John Witmer  Word  2  02042017 02:35 PM 
Spell check on leaving a cell  WPeacock  Excel Programming  0  02212016 06:05 PM 
Double click on a cell to add check mark?  robbinskelly7  Excel  1  02092015 07:05 PM 
need to check whether cell contains formula  etodem  Excel  2  10232010 07:33 PM 