Unable to use INDIRECT function in Conditional Formatting

Hi everyone.

I know I will be told to 'get with the times' (which I am being forced to do anyway!) but I still use Excel 2003. I am having to transition a formula driven sheet to work in Excel 2010 and all the Conditional Formatting is lost when this is done (not big deal - I can put it all back in). The problem I am having is that what worked in 2003 does not work in 2010 - the latest version (to my mind) has regressed.

I have attached a much simplified version of the sheet (Book1.xls in 2003 version) with the CF formula part in cell B4 that does not work in 2010.

Book1.xls

I want to use the INDIRECT function in CF but 2010 returns an error "May not use reference operations (such as unions, intersections and ranges) or arrays blah blah........". I know there are other posts referring to this same message but I have not found one that solves what I want to do.

I basically want to reformat cell B4 when the COUNTIF value for letter "O" in a variable range of cells decided by [C4:INDIRECT(C2)], is >= cell E2.
It works perfectly in 2003.

I know there are other ways to do it, but they are clumsy - is there a tidy way of accomplishing this?

All help appreciated.
Regards.

May not be what you're after but if you put your condition

Please Login or Register  to view this content.

into a spare cell (say K1)

Then you can conditionally format B4 based on the simple condition

Please Login or Register  to view this content.

If the condition is TRUE it'll format, otherwise it won't.

Thanks for your quick reply OfficeCoach.

That does work yes, but as I have many different rows where this needs to be used in, and the rows are not uniform in the number of columns they have, I was trying to avoid using 'holding cells' due to a mass of other formatting, merged cells, and other bit's n bobs which stand in the way of where these cells could go (but yes, I know I could plug them in and hide them off the viewable part of the spreadsheet).

I am just very surprised that 2010 has stopped allowing functions such as INDIRECT being used in CF - why? If it worked fine in early versions then why reduce the capability.... seems odd!

If this is the only real way to go then OK, but is there a tidier way?

Brilliant! Yep - works.

It had never crossed my mind that the range was the problem! I was interpreting the error message incorrectly....

Thanks a bunch - appreciated

Regards.

And thanks for the alternative way - every-day's a learning day

I guess I need to familiarise myself with the newer syntax requirements of 2010 (and upwards)

Cheers!