Excel 2007 : Conditional Formatting formula

**FortuneKid** · 07-06-2011, 03:05 PM

What I have is a dataset that is organzied by year and then by quarter within each year.
[ 2011 ]
[1][2][3][4]

I also have data that has a "launch year" "launch quarter" "out year" and "out quarter" (as in out of production)

To conditionally format it I've alredy got the formula to determine where to highlight the years but I'm having trouble then erasing or adding to the fill depending on the quarter data.

Here's what I've got so far.
=AND($A7<=G$3,OR($C7>=G$3,$C7=0))
A7 is the launch year
C7 is the out year

G3 is the year header as shown above (2011, 2012, etc.)

B7 is the launch quarter
D7 is the out quarter

Any help with the quarter thing is MUCH appreciated!!!!

**arthurbr** · 07-06-2011, 03:14 PM

Hi and welcome to the board

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**FortuneKid** · 07-06-2011, 03:29 PM

Here's a dummy book of the problem.

THANKS!!!!

**arthurbr** · 07-07-2011, 02:15 AM

Does this help ? ( see attached)

You could dispense with row 4 and replace the G4 reference with left(g4,1) in the formula for CF

**FortuneKid** · 07-07-2011, 08:08 AM

That seemed to work for the right out year/quarter side but look how Q1 is still marked even though it doesn't begin until Q2 in 2011.

We're very close though, I didn't think about the "&" function.

also I originally tried using the LEFT thing but for some reason when I'd test it with a basic equation like LEFT(G4,1)<8 it wouldn't yeild a correct answer as if it wasn't really seeing the number or something.

**FortuneKid** · 07-07-2011, 08:23 AM

Ahhhh! Thank you aurthorB, you laid the groundwork and I was able to figure it out using your & suggestion!!!!

Final formula for the formatting: =AND($A7&$B7<=G$3&G$4,OR($C7&$D7>=G$3&G$4,$C7=0))

Brilliant!!!

**arthurbr** · 07-07-2011, 10:09 AM

Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save