Lookup date in range then find on another sheet

[Cuzman]

I have been trying to find a way i can automate this work...
I have a list of dates that i enter on to sheet 1. I then have to go onto sheet 2 or 3 and find those dates and highlight that column in red.

I'm sure this can be done with a macro but can't think where to start...can anyone help?

 

[gsbelbin]

Hi
I'm assuming that on your sheets 2 and 3 the dates are the column headers. If this isn't the case then this won't work but it might give you an idea to develop.

You can do this with Conditional Formatting but it just needs a bit of extra work. First of all define a dynamic named range so that the list of dates can be any length. Go Insert | Name | Define. Type a name in the top box like Dates. Then in the "Refers to" box type

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

but change this to your sheet name, column and the first cell that you will have the dates in. Then click Add and Close. This gives you a flexible named list of dates that other formulae can refer to.

Go to your other sheet(s) and select the columns that have the dates as the headers. Go Format |Conditional Formatting and select "Formula is" from the drop down. In the box next to this enter the following (again alter the start cell to suit your data)

=IF(ISERROR(VLOOKUP(A$1,Dates,1,FALSE)),1,0)=0

Choose the formatting you want and click okay.

This should be all you need to do. All the cells in a given column will be formatted if the date in the header row appears in the list of dates on the other sheet.

Cheers

Gordon

 

[Cuzman]

Thanks Gordon,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
That works well but unfortunately only on the first sheet…not the second…for some reason it is highlighting random columns.<o></o>
You are right in thinking I have dates in the column header on sheets 2 and 3…<o></o>
sheet2 has dates 01/01/11 – 30/6/11 and the remainder of the year on sheet3<o></o>
<o> </o>
The dates are on sheet1 starting at F19 therefore I have defined the following as Dates<o></o>
=OFFSET(sheet1!$F$19,0,0,COUNTA(sheet1!$F:$F),1)<o></o>
<o> </o>
On sheets1&2 I have entered =IF(ISERROR(VLOOKUP(A$2,Dates,1,FALSE)),1,0)=0
<o> </o>
As the dates are actually in row 2…
<o> </o>
Does this correct?
<o> </o>
Mark

 

[gsbelbin]

Hi Mark
That looks correct to me. I can't think what the problem with the second sheet might be other than the way the dates are entered. Sometimes it's difficult to convince Excel that the dates you've entered are not American format. Also make sure that the formatting formula is exactly the same for both sheets.

Cheers

Gordon

 

[Cuzman]

Well thanks again for your help Gordon...I'll have a play and see if i can get it to work.

Mark