Here is a stumper for you (programming).....

[GoBrowns!]

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!!!!!!!!!!!!

 

[smartin]

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)

 

[JLGWhiz]

Maybe you could talk him into this:

http://office.microsoft.com/en-us/templates/TC010184581033.aspx

 

[JLGWhiz]

Here is another option.

http://www.spreadsheetstore.com/c-6-inventory-management-software-for-excel.aspx

 

[GoBrowns!]

I need to do this analysis for 1900 lines of data, currently. I am not sure
if how you have suggested would work. The way I am envisioning that that my
boss could just click a check box (maybe?) to determine which buffers he
wants to include, then I could have a column that would add his selections
together?

Is that possible????

 

[smartin]

I suggest, before you make things more complicated than need be, try the
worksheet solution. It will work with proper adjustments for ranges.

A form or battery of checkboxes might well be be a nice touch for the
UI, but these will add to the complexity (because you will have to
handle the state of the objects) whilst the underlying functionality
(formatting and formulas) will still need to be there, similar to as I
described. Once you get the fundamental technique down, we can expand on it.

 

[GoBrowns!]

I played around with this and finally figured out what you were talking
about, and it worked like a charm.

I am going to leave it as is and if my boss wants check boxes, etc. I will
be back....

Thanks so much for your help!!!!!