Data Validation List with Week Ending Dates

[jdawg_1989]

Hi, I have a data validation list. In the list I have the week ending (Friday) dates from 07-Jan-11 to 30-Mar-12.

This is so the user can look at the Program Statistics for any given week that the Program has run.

Am I able to set it up so when you click the drop down the current week ending date is displayed first, rather than 07-Jan-11?

You would then scroll up for historical dates and down for new dates?

 

[T. Valko]

Hi, I have a data validation list. In the list I have the week ending (Friday) dates from 07-Jan-11 to 30-Mar-12.

This is so the user can look at the Program Statistics for any given week that the Program has run.

Am I able to set it up so when you click the drop down the current week ending date is displayed first, rather than 07-Jan-11?

You would then scroll up for historical dates and down for new dates?

You can put the Friday date for the current week at the top of the date list.

This will return the Friday date for the current week (week running from Mon to Sun).

=TODAY()-WEEKDAY(NOW(),2)+5

 

[jdawg_1989]

You can put the Friday date for the current week at the top of the date list.

This will return the Friday date for the current week (week running from Mon to Sun).

=TODAY()-WEEKDAY(NOW(),2)+5

Thanks for the quick response! I did consider doing this for the first 8 dates in the list, see below

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #FF0000;;">Week Ending</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;font-style: italic;background-color: #FF0000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20/05/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">27/05/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">03/06/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10/06/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">17/06/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">24/06/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">01/07/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">08/07/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Pivots</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A5</th><td style="text-align:left">=A6-7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A6</th><td style="text-align:left">=A7-7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A7</th><td style="text-align:left">=A8-7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A8</th><td style="text-align:left">=A9-7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A9</th><td style="text-align:left">=A10-7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A10</th><td style="text-align:left">=A11-7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A11</th><td style="text-align:left">=TODAY(<font color="Blue"></font>)+7-WEEKDAY(<font color="Blue">NOW(<font color="Red"></font>),2</font>)+5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A12</th><td style="text-align:left">=A11+7</td></tr></tbody></table></td></tr></table><br />

The thing I am worried about is when the report gets published, when it gets opened a a year later will the date selected from the drop down default to the formula selected from the data validation list?

Or will the date selected at the time stay the same?

 

[T. Valko]

Thanks for the quick response! I did consider doing this for the first 8 dates in the list, see below

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; FONT-STYLE: italic; BACKGROUND-COLOR: #ff0000">Week Ending</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; FONT-STYLE: italic; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">20/05/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">27/05/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">03/06/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">10/06/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">17/06/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">24/06/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">01/07/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right">08/07/2011</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Pivots​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>A5</TH><TD style="TEXT-ALIGN: left">=A6-7</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>A6</TH><TD style="TEXT-ALIGN: left">=A7-7</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>A7</TH><TD style="TEXT-ALIGN: left">=A8-7</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>A8</TH><TD style="TEXT-ALIGN: left">=A9-7</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>A9</TH><TD style="TEXT-ALIGN: left">=A10-7</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>A10</TH><TD style="TEXT-ALIGN: left">=A11-7</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>A11</TH><TD style="TEXT-ALIGN: left">=TODAY()+7-WEEKDAY(NOW(),2)+5</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>A12</TH><TD style="TEXT-ALIGN: left">=A11+7</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


The thing I am worried about is when the report gets published, when it gets opened a a year later will the date selected from the drop down default to the formula selected from the data validation list?

Or will the date selected at the time stay the same?

If the cell that contains the data validation list is empty the drop down list will be displayed starting from the first (top) entry.

If the cell already contains a selection from the list, when the drop down listed is selected it will automatically scroll to that selection.

It sounds like this is not what you want to happen.

I don't know how you'd stop it from doing that. Maybe with some VBA code but I don't know how to do it.

 

[jdawg_1989]

Say for example my Cell with the data Validation is stored in B1 and my data validation list is in A4:A12

I am going to select B1 to give me my drop down list and select 01/07/11 which is the value stored in A11 with formula =TODAY()+7-WEEKDAY(NOW(),2)+5

I will now save this file, and open in in 3 months time, I know the data validation list will be recalculated, but will the value "01/07/11" in cell B1 stay the same? Or will it update to A11?

 

[T. Valko]

Say for example my Cell with the data Validation is stored in B1 and my data validation list is in A4:A12

I am going to select B1 to give me my drop down list and select 01/07/11 which is the value stored in A11 with formula =TODAY()+7-WEEKDAY(NOW(),2)+5

I will now save this file, and open in in 3 months time, I know the data validation list will be recalculated, but will the value "01/07/11" in cell B1 stay the same? Or will it update to A11?

Cell B1 will stay the same.

 

[jdawg_1989]

Cell B1 will stay the same.

Excellent! Thanks, that'll do me! Thanks for your help and clarification

 

[T. Valko]

Excellent! Thanks, that'll do me! Thanks for your help and clarification

Good deal. Thanks for the feedback!