?
Solved

Need additional help with data validation

Posted on 2011-03-17
5
Medium Priority
?
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

719 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