Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here is a stumper for you (programming).....
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!!!!!!!!!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here is a stumper for you (programming).....
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here is a stumper for you (programming).....
Maybe you could talk him into this:
http://office.microsoft.com/en-us/te...184581033.aspx "GoBrowns!" wrote in message ... 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!!!!!!!!!!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here is a stumper for you (programming).....
Here is another option.
http://www.spreadsheetstore.com/c-6-...for-excel.aspx "GoBrowns!" wrote in message ... 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!!!!!!!!!!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here is a stumper for you (programming).....
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" wrote: 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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here is a stumper for you (programming).....
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! wrote: 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" wrote: 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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here is a stumper for you (programming).....
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!!!!! "smartin" wrote: 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! wrote: 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" wrote: 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) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here is a stumper for you (programming).....
Glad you got it working. Sounds like a plan.
GoBrowns! wrote: 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!!!!! "smartin" wrote: 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! wrote: 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" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet formatting stumper!! (repost) | Excel Discussion (Misc queries) | |||
Worksheet formatting stumper! | Excel Discussion (Misc queries) | |||
A bit of a Stumper | Excel Worksheet Functions | |||
Conditional Format Stumper | Excel Discussion (Misc queries) | |||
Stumper WORKDAYS Problem | Excel Discussion (Misc queries) |