Solved

Need additional help with data validation

Posted on 2011-03-17
5
177 Views
Last Modified: 2012-05-11
Someone helped me earlier with my question regarding disallowing weekend or holiday dates into a cell.  Here was the formula...

=AND(WEEKDAY(A1)<>1,WEEKDAY(A1)<>7,ISERROR(MATCH(DATE(DAY(A1),MONTH(A1),YEAR(TODAY())),DATE(DAY(E1:E6),MONTH(E1:E6),YEAR(TODAY())),0)))

But now my problem is that I enter this in data validation and it works fine until I save the file and re-open the file.  Once this has been done the data validation no longer stays in place.

????
0
Comment
Question by:SteveL13
  • 3
5 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35157135
What to you mean by " the data validation no longer stays in place."?
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35157148
Hello Steve,

Did you try my suggestion?

=AND(WEEKDAY(E2,2)<6,COUNTIF(H$1:H$10,E2)=0)

where H1:H10 is the holiday range - I don't get any problem when I shut it down - which version of excel are you using?

barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35157210
Here's a sheet with that working - does it work for you?

barry
26893791.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35157223
and in 2003 format if you need that....

barry
26893791v2.xls
0
 

Author Closing Comment

by:SteveL13
ID: 35157231
This seems to be working.  Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now