Conditional formatting formula

[Ahoneysett]

I am trying to apply conditional formatting to a cell, using a reference to another sheet. I believe this can be done using named ranges or the indirect function.

My named range is current_date which is in sheet: dates B3. I want to compare this to another date in the same tab i.e. dates A3.

I have entered the following into the conditional formatting formula:

=("'current_date'">indirect("'dates'!a3") and am getting a message that I am missing a parenthesis.

The reason I am using indirect is because I cannot name the range in a3 because it starts at 01/01/2009 and goes down to 2017.

Can anyone suggest a solution?

Thanks

[jj72uk]

Have you put 'Formula Is' in the drop down box and take off the '=' sign

[ChemistB]

your formula

Please Login or Register  to view this content.

has a few issues.
First, and what Excel caught, is you do not need that first ( before "'current_date'".
Secondly, you shouldn't need any of those single or double quotes, remove them.
Unfortunately, Excel will not let you use references to other sheets even using the INDIRECT function so you will still get an error.

So what you are saying in your post is that the range you want to refer to is not a single cell but a series of cells starting in dates!A3 to dates!A2000 or so? I'll try a few things to see if I can figure this out. Hopefully a mod will come along and help.

[Ahoneysett]

Thanks for your help.

I have a list of dates in the dates sheet i.e.

A B
Future Date Current Date

25/01/2009 30/10/2009
25/02/2009
25/03/2009
25/04/2009
25/05/2009
25/06/2009
25/07/2009
25/08/2009
25/09/2009
25/10/2009
25/11/2009
25/12/2009
25/01/2010
25/02/2010

With the conditional formatting, if the current date is greater than say call A8 (25/06/2009) I want the cell to turn red. The next cell will refer to A9 etc. I am trying to do this in a different sheet.