GoBrowns! wrote:
I am not even sure if this can be done, but here goes...

My boss wants an Excel worksheet containing the following Headings (among
others):

Supply Variability Buffer
Demand Variability Buffer
Forecast Error Buffer
Total Safety Stock (which is the sum of the top three buffers listed)
Inventory by Day (for instance, 4/1/09, 4/2/09, 4/3/09, etc. as their own
headers)

Would it be possible to allow my boss to choose which of the buffers he
wants to add together (as one possible choice, just the supply and demand
buffers added together) and then have it use conditional formatting to shade
every inventory cell that was a lower quantity than the buffers he just added
together?

For instance, if he added the Supply Buffer (qty 14) and the Demand Buffer
(qty 5) together, I would want all inventory boxes on that line that were
less than qty19 shaded red. However, if he wanted to choose just the supply
buffer and no other buffers to add it to, shading would need to occur then as
well, using the same parameters.

Finally, I would need to be able to tally up the number of shaded cells on
each line (using SUMPRODUCT maybe?).. I am not sure how to do this if the
inputs (which buffers he is using) is constantly changing.

Well, is it totally impossible? I am at a loss.... any additional questions
I can answer I would be happy to do so..... thanks for all your
help!!!!!!!!!!!!


You do not need VBA for this; it can all be done in the worksheet.

Near the Buffers allow a cell to indicate selection, like this:

A B C
1 Buffer Select? Value
2 Supply Y 14
3 Demand Y 5
4 Forecast 21
5 Total 40
6 Total Selected 19

The formula for Total Selected is
=SUMIF(B2:B4,"y",C2:C4)

Now you can reference the total selected value for the conditional
formatting. The CF formula over the range of cells in question is as
simple as Value Is | Less Than | $C$6.

For the sum of the values below threshold, let's say you have some data
in C9:Q9. On this row the sum of shaded cells will be
=SUMIF(C9:Q9,"<"&$C$6)