Data Validation Help

[dwgnome]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I would like to use Data Validation (DV) to prevent certain cells from not being entered in a Year schedule table. I have used conditional formatting to make weekend cells turn yellow. It is these cells I want to prevent data from being entered.<o></o>
<o></o>

The table has headers Jan through Dec in Row 4 starting in Column C. I have the days 1 through 31 listed in each row starting at B5.<o></o>
<o></o>

The CF are the following:<o></o>
<o></o>

Turns cell Yellow if Weekend<o></o>
<o></o>
=WEEKDAY(DATE($C$3,COLUMNS($C$4:C4),ROWS($B$5:B5)),2)>5<o></o>
<o></o>

Turns cell Pink if a <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Holiday</st1lace> as listed in S5:S12:<o></o>
<o></o>
=MATCH((DATE($C$3,COLUMNS($C$4:C4),ROWS($B$5:B5))),$S$5:$S$12,0)<o></o>
<o></o>
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_s1026 style="MARGIN-TOP: 4pt; Z-INDEX: 1; MARGIN-LEFT: 81pt; WIDTH: 315.75pt; POSITION: absolute; HEIGHT: 126pt; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" type="#_x0000_t75"><v:imagedata o:title="" src="file:///C:\DOCUME~1\David\LOCALS~1\Temp\msohtml1\01\clip_image001.png"></v:imagedata></v:shape>How can I use the Weekend formula in DV to accomplish my goal? When I copied the Weekend formula into custom formula and then copy down or across Excel prevents me from entering letters in all of the cells not just the yellow ones.<o></o>
<o></o>

<o></o>
<o></o>
<o></o>
<o></o>
<o></o>
<o></o>
<o></o>
<o></o>
<o></o>
<o></o>
<o>
Vacation 11

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 83px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Year:</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #000080; TEXT-ALIGN: center">2011</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 50px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc00; TEXT-ALIGN: center">Month/Day</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">Jan</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">Feb</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">Mar</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C3</TD><TD>=2011</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #0000ff; BORDER-BOTTOM-COLOR: #0000ff; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #0000ff; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #0000ff; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Data Validation in Spreadsheet</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Allow</TD><TD>Datas</TD><TD>Input 1</TD><TD>Input 2</TD></TR><TR><TD>C5</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:C4),ROWS($B$5:B5)),2)>5</TD><TD></TD></TR><TR><TD>D5</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:D4),ROWS($B$5:C5)),2)>5</TD><TD></TD></TR><TR><TD>E5</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:E4),ROWS($B$5:D5)),2)>5</TD><TD></TD></TR><TR><TD>C6</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:C5),ROWS($B$5:B6)),2)>5</TD><TD></TD></TR><TR><TD>D6</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:D5),ROWS($B$5:C6)),2)>5</TD><TD></TD></TR><TR><TD>E6</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:E5),ROWS($B$5:D6)),2)>5</TD><TD></TD></TR><TR><TD>C7</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:C6),ROWS($B$5:B7)),2)>5</TD><TD></TD></TR><TR><TD>D7</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:D6),ROWS($B$5:C7)),2)>5</TD><TD></TD></TR><TR><TD>E7</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:E6),ROWS($B$5:D7)),2)>5</TD><TD></TD></TR><TR><TD>C8</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:C7),ROWS($B$5:B8)),2)>5</TD><TD></TD></TR><TR><TD>D8</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:D7),ROWS($B$5:C8)),2)>5</TD><TD></TD></TR><TR><TD>E8</TD><TD>Custom</TD><TD></TD><TD>=WEEKDAY(DATE($C$3,COLUMNS($C$4:E7),ROWS($B$5:D8)),2)>5</TD><TD></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #ff0000; BORDER-BOTTOM-COLOR: #ff0000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #ff0000; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #ff0000; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Conditional formatting </TD></TR><TR><TD><TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=0 border=1><TBODY><TR><TD>Cell</TD><TD>Nr.: / Condition</TD><TD>Format</TD></TR><TR><TD>C5</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:C4),ROWS($B$5:B5)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>C5</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:C4),ROWS($B$5:B5))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>D5</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:D4),ROWS($B$5:C5)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>D5</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:D4),ROWS($B$5:C5))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>E5</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:E4),ROWS($B$5:D5)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>E5</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:E4),ROWS($B$5:D5))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>C6</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:C5),ROWS($B$5:B6)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>C6</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:C5),ROWS($B$5:B6))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>D6</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:D5),ROWS($B$5:C6)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>D6</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:D5),ROWS($B$5:C6))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>E6</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:E5),ROWS($B$5:D6)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>E6</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:E5),ROWS($B$5:D6))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>C7</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:C6),ROWS($B$5:B7)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>C7</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:C6),ROWS($B$5:B7))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>D7</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:D6),ROWS($B$5:C7)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>D7</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:D6),ROWS($B$5:C7))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>E7</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:E6),ROWS($B$5:D7)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>E7</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:E6),ROWS($B$5:D7))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>C8</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:C7),ROWS($B$5:B8)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>C8</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:C7),ROWS($B$5:B8))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>D8</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:D7),ROWS($B$5:C8)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>D8</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:D7),ROWS($B$5:C8))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR><TR><TD>E8</TD><TD>1. / Formula is =WEEKDAY(DATE($C$3,COLUMNS($C$4:E7),ROWS($B$5:D8)),2)>5</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>E8</TD><TD>2. / Formula is =MATCH((DATE($C$3,COLUMNS($C$4:E7),ROWS($B$5:D8))),$S$5:$S$12,0)</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000; FONT-STYLE: italic; BACKGROUND-COLOR: #ff99cc">Abc</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4 <o></o>
<o></o>
<o></o>
</o>
<o></o>
<o></o>

 

[AlphaFrog]

Try something like this for the DV formula in C5

=NOT(AND(C5<>"", WEEKDAY(DATE($C$3,COLUMNS($C$4:C4),ROWS($B$5:B5)),2)>5))

 

[dwgnome]

Thank you AlphaFrog, that worked!


Another related question:

Is there a way to combine the answer you provided with the additional CF concept of preventing entries other than an H for the Holiday or pink cells? Is there a way to automatically have an H placed in the pink cells?

 

[dwgnome]

Bump.

 

[AlphaFrog]

combine the answer you provided with the additional CF concept of preventing entries other than an H for the Holiday or pink cells

DV formula for cell C5

=NOT(OR(AND(WEEKDAY(DATE($C$3,COLUMN(A:A),$B5),2)>5, LEN(C5)), AND(COUNTIF($S$5:$S$12,DATE($C$3,COLUMN(A:A),$B5)), UPPER(C5)<>"H")))

Is there a way to automatically have an H placed in the pink cells?

I don't know a way to do that with just CF or DV. It could be done with a formula in each cell (which I don't think you want) or it could be done with a macro.

 

[dwgnome]

Thank you again AlphaFrog. Your solution was spot on.

As for the placing of the "H" in the cells, you're correct, I cannot have formulas in the cells. I would however, entertain VB/macro idea if you or someone else would provide that.

Cheers.