Conditional format date range based on drop down menu

[jmpiper]

Okay, this is a little sticky, so I hope I can articulate it properly so that it makes sense. I have a drop-down menu at the top of a worksheet which contains the following values: Q1-2012, Q2-2012, Q3-2012, and Q4-2012. When one of these is selected I would like a cell to search a column of data and find a date that falls within the range indicated by the drop down selection (Example: selecting Q1-2012 would prompt a search for dates between 1/1/2012 and 3/31/2012). The conditional format formula would then highlight all cells within the date range column a certain color. If Q2-2012 is selected from the drop down menu then the conditional format formula would search the column of data and highlight the dates that fall within that specified quarter.

Make sense?

Anyone have experience with this type of conditional formatting?

Many thanks,

Jeff

 

[Marcelo Branco]

A possible solution

Create this table in, say, X1:Z4

X Y Z

Q1-2012	01/01/2012	03/31/2012
Q2-2012	04/01/2012	06/30/2012
Q3-2012	07/01/2012	09/30/2012
Q4-2012	10/01/2012	12/31/2012

<TBODY>
</TBODY>

A

Quarter

Q2-2012

Dates

01/01/2012

01/11/2012

01/21/2012

01/31/2012

02/10/2012

02/20/2012

03/01/2012

03/11/2012

03/21/2012

03/31/2012

04/10/2012

04/20/2012

04/30/2012

05/10/2012

05/20/2012

05/30/2012

06/09/2012

06/19/2012

06/29/2012

07/09/2012

07/19/2012

07/29/2012

08/08/2012

08/18/2012

08/28/2012

09/07/2012

09/17/2012

09/27/2012

10/07/2012

10/17/2012

10/27/2012

11/06/2012

11/16/2012

11/26/2012

12/06/2012

12/16/2012

12/26/2012

<TBODY>
</TBODY>

Select A2
Data > Data Validation

Allow: List
Source:$X$1:$X$4


Select all the dates (A5:A41 in the example above) being the first cell (A5 in the example above) the active cell, ie, the one that is not shaded after the selection

Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format

insert this formula
=$A5=MEDIAN($A5,VLOOKUP($A$2,$X$1:$Z$4,2,0),VLOOKUP($A$2,$X$1:$Z$4,3,0))
Format button
Fill --> yellow (for example)

Ok, Ok

Done!

M.