Solved

Need additional help with data validation

Posted on 2011-03-17
5
185 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

740 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