Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running Sum?
W/O 10 UPL 330
Product Pass 1 Pass 2 Pass 3 Pass 4 Total W/O's 10 10 10 10 10 4 20 10 10 10 10 4 30 10 10 10 10 4 40 10 10 10 10 4 50 10 10 10 10 4 60 10 10 10 10 4 70 10 1 80 10 10 10 10 4 90 10 10 2 100 10 10 10 10 4 100 90 80 80 Hi all I need some help with a spreadsheet. I have data arranged in columns that will typically be values of ten. Based on some other criteria the cell will or will not contain the value ten after successive passes. What I want to do is tally up to a predetermined value (UPL) and have an operator tell at a glance (maybe colored cells or something) where to stop. So in the example included, the operator would pull all work orders for the first three columns since the total is 260. I would like to have a visual indicator that tell them to then work down the Pass 4 column until 330 is reached in this case the final three units of ten would not need to be pulled. The next day it may be all of the first three columns and the first three of the fourth column. Any ideas for a quick fix on this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running Sum?
Run this macro and you'll get your desired result. You may need to adjust
the locations. Sub GetUPL() Dim UPL As Double Dim CumIt As Double Dim Found As Integer Cells.Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Let UPL = Cells(2, 5).Value 'this is the value you wish to work up to Let X = 4 'this is the first row of your 10 rows of data For Y = 2 To 5 For X = 4 To 13 Let CumIt = CumIt + Cells(X, Y) If CumIt = UPL Then Found = 1 Exit For End If Next If Found = 1 Then Exit For Next If Found = 1 Then Cells(X, 5).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If End Sub "RichieK" wrote: W/O 10 UPL 330 Product Pass 1 Pass 2 Pass 3 Pass 4 Total W/O's 10 10 10 10 10 4 20 10 10 10 10 4 30 10 10 10 10 4 40 10 10 10 10 4 50 10 10 10 10 4 60 10 10 10 10 4 70 10 1 80 10 10 10 10 4 90 10 10 2 100 10 10 10 10 4 100 90 80 80 Hi all I need some help with a spreadsheet. I have data arranged in columns that will typically be values of ten. Based on some other criteria the cell will or will not contain the value ten after successive passes. What I want to do is tally up to a predetermined value (UPL) and have an operator tell at a glance (maybe colored cells or something) where to stop. So in the example included, the operator would pull all work orders for the first three columns since the total is 260. I would like to have a visual indicator that tell them to then work down the Pass 4 column until 330 is reached in this case the final three units of ten would not need to be pulled. The next day it may be all of the first three columns and the first three of the fourth column. Any ideas for a quick fix on this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running Sum?
Mike
First of thanks a lot! I really have to get better at the VBA part of thing so life can be easier! Overall the your advice provided a solid solution. Some of the property settings (TintAndShade, PatternTintAndShade) do not work but I assume those are for a different version of Excel I am using Excel 2003. If I may ask a two part follow-up question I have included the macro as it is today below. 1. How do I change the font of the left-over cells to white or light gray to make it less possible for operators to pull work orders for those part numbers. 2. Also is there a method to make the Regent(below) name a variable so that font is set to bold based on the current worksheet? Worksheets("Regent").Cells(x, y).Font.Bold = True Thank you Rich k Sub WO_List() ' ' 'Run this macro and you'll get your desired result. You may need to adjust 'the locations. Dim UPL As Double Dim CumIt As Double Dim Found As Integer Range("u6:aa16").Select With Selection.Interior .Pattern = xlNone Selection.Font.Bold = False ' .TintAndShade = 0 ' .PatternTintAndShade = 0 End Let CumIt = 0 Let UPL = Cells(2, 25).Value 'this is the value you wish to work up to Let x = 7 'this is the first row of your 10 rows of data For y = 23 To 27 For x = 7 To 16 CumIt = CumIt + Cells(x, y) If CumIt = UPL Then Found = 1 Exit For End If Next If Found = 1 Then Exit For Next If Found = 1 Then Cells(x, y).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 ' .Font = Bold ' .TintAndShade = 0 ' .PatternTintAndShade = 0 End With Worksheets("Regent").Cells(x, y).Font.Bold = True End If End Sub "Mike H." wrote: Run this macro and you'll get your desired result. You may need to adjust the locations. Sub GetUPL() Dim UPL As Double Dim CumIt As Double Dim Found As Integer Cells.Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With Let UPL = Cells(2, 5).Value 'this is the value you wish to work up to Let X = 4 'this is the first row of your 10 rows of data For Y = 2 To 5 For X = 4 To 13 Let CumIt = CumIt + Cells(X, Y) If CumIt = UPL Then Found = 1 Exit For End If Next If Found = 1 Then Exit For Next If Found = 1 Then Cells(X, 5).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If End Sub "RichieK" wrote: W/O 10 UPL 330 Product Pass 1 Pass 2 Pass 3 Pass 4 Total W/O's 10 10 10 10 10 4 20 10 10 10 10 4 30 10 10 10 10 4 40 10 10 10 10 4 50 10 10 10 10 4 60 10 10 10 10 4 70 10 1 80 10 10 10 10 4 90 10 10 2 100 10 10 10 10 4 100 90 80 80 Hi all I need some help with a spreadsheet. I have data arranged in columns that will typically be values of ten. Based on some other criteria the cell will or will not contain the value ten after successive passes. What I want to do is tally up to a predetermined value (UPL) and have an operator tell at a glance (maybe colored cells or something) where to stop. So in the example included, the operator would pull all work orders for the first three columns since the total is 260. I would like to have a visual indicator that tell them to then work down the Pass 4 column until 330 is reached in this case the final three units of ten would not need to be pulled. The next day it may be all of the first three columns and the first three of the fourth column. Any ideas for a quick fix on this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
running sum | Excel Discussion (Misc queries) | |||
Keep Running Tab | Excel Discussion (Misc queries) | |||
Running Totals | Excel Discussion (Misc queries) | |||
Running Averages | New Users to Excel | |||
Running Add-In from VBA | Excel Discussion (Misc queries) |