Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need additional help with data validation

Posted on 2011-03-17
5
Medium Priority
?
196 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

885 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