conditional formatting for expanding worksheet

[Kimber]

Each day I get a report with several products listed with their daily plan and listed below each day in the period -- as it occurs. In other words, on the second day of the production period there is one entry for day one, on the third day there are two entries, etc. up to the end of the period. Each day I highlight the cells for teh plan and for the previous day's number, depending on which is greater. (Yellow if we produced equal to or more than the plan; pink if we produced less than the plan). Each product section grows by a row over the course of the period, so I cannot compare absolute cell references each day. Is there a formula that will compare the plan cell (absolute) to the last number in the list and then apply conditional formatting? Also, there are roughly 35 product blocks in each column and the maximum amount of production days is 25, so the formula needs to stop comparing at the 25th or less cell in that group. I hope this makes sense. Thank you for any suggestions.

 

[Luke M]

Rough idea of what your sheet looks like:


Our plan (data starts in B2)

Apples | 50

Oranges | 20

Pears | 10


Then, starting on row 10:

1/1/2011 | Apples | 10

1/2/2011 | Apples | 20

1/1/2011 | Oranges | 30

etc.


Is this (somewhat) correct? So, we just need the CF on the plan numbers. I think this will work:

=B2>=INDEX(C:C,MAX(IF(B$10:B$1000=A2,ROW(B$10:B$1000))))

Format as Yellow


=B2<INDEX(C:C,MAX(IF(B$10:B$1000=A2,ROW(B$10:B$1000))))

Format as Pink


formulas work by returning data from last row that has a product matching product name you are searching for.

 

[Kimber]

Thank you, Luke. I didn't think I had posted this question, so forgive my repeat. Your scenario is close, but not quite what I need. I cannot upload an example. Can I email a sample worksheet to you directly? Thank you for your help.