Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-06-2017, 05:26 AM
Jelmer Jelmer is offline Check for value in each cell separately Windows Vista Check for value in each cell separately Office 2016
Novice
Check for value in each cell separately
 
Join Date: May 2017
Location: Netherlands/UK
Posts: 15
Jelmer is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 07-06-2017, 06:35 AM
gebobs gebobs is offline Check for value in each cell separately Windows 7 64bit Check for value in each cell separately Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

How about using data validation to preclude incorrect entry of data?
Reply With Quote
  #3  
Old 07-06-2017, 06:44 AM
Jelmer Jelmer is offline Check for value in each cell separately Windows Vista Check for value in each cell separately Office 2016
Novice
Check for value in each cell separately
 
Join Date: May 2017
Location: Netherlands/UK
Posts: 15
Jelmer is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 07-06-2017, 07:01 AM
gebobs gebobs is offline Check for value in each cell separately Windows 7 64bit Check for value in each cell separately Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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. :-)
Reply With Quote
  #5  
Old 07-06-2017, 07:43 AM
Jelmer Jelmer is offline Check for value in each cell separately Windows Vista Check for value in each cell separately Office 2016
Novice
Check for value in each cell separately
 
Join Date: May 2017
Location: Netherlands/UK
Posts: 15
Jelmer is on a distinguished road
Default

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

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
Check for value in each cell separately 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
Check for value in each cell separately Double click on a cell to add check mark? robbinskelly7 Excel 1 02-09-2015 07:05 PM
Check for value in each cell separately need to check whether cell contains formula etodem Excel 2 10-23-2010 07:33 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:44 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft