#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 | 04-04-2017 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 | 02-04-2017 02:35 PM |
Spell check on leaving a cell | WPeacock | Excel Programming | 0 | 02-21-2016 06:05 PM |
Double click on a cell to add check mark? | robbinskelly7 | Excel | 1 | 02-09-2015 07:05 PM |
need to check whether cell contains formula | etodem | Excel | 2 | 10-23-2010 07:33 PM |