scheduling sheet [SOLVED]

[kocakb]

Hello,


i'm making a shift schedule planning sheet.


As you can see in sample file ( http://sdrv.ms/12IKf2w )


i have product names in rows, dates in columns and shifts in columns grouped by dates.


If there is work planned, i'm putting 1 in shift cell.(ie AF3)


then, in other part of sheet, i want to sum these shift figures by date.


for example, for July 11, in AF3, AG3 and AH3, there are 1s. i want to sum them these shifts in EJ3 by july 11.


and this will repeat for all SKUs.


i used several formulas with indirect, address, column. but they were not practical.


I wanted you, excel gurus to get your valuables ideas which can be more efficient to sum these values more efficiently.


Best regards,


Biray

 

[crouchsw]

I would create a UDF. In VBA create a module, and paste the following in:

[pre]

Public Function sumarray(Lookup As Date, Table As Range)

Dim numcols As Integer
Dim numrows As Integer
Dim runningsum As Double
Dim checkdate As Date

numcols = Table.Columns.Count
numrows = Table.Rows.Count

If numrows <= 2 Then
sumarray = "Insufficient number of rows selected"
Exit Function
End If

For d = 2 To numcols
If Cells(1, d) = 0 Then GoTo skip
checkdate = Cells(1, d)
skip:
If checkdate = Lookup Then
For p = 3 To numrows
runningsum = runningsum + Cells(p, d).Value
Next
End If
Next
sumarray = runningsum
End Function

[/pre]
In the cells EJ3 and on, type =sumarray(EJ1,$B$1:$DW$10)

 

[Hui]

Biray


You haven't told us how much each resource has or requires and so I don't see how you can determine say the length of any of the Production Runs


The post at:

http://chandoo.org/wp/2010/11/18/scheduling-variable-sources/

explains a technique for automatic scheduling but it requires you to know how much of each product you have and use per period.

 

[kocakb]

Hello Stephen,


your udf works for EJ3


unfortunately, it gives wrong results for other cells;


for example, for july 8, for product1, total had to be zero, but it calculates "3" which sums all july 8th shifts (all rows)


i think there has to be some correction in the udf.


i also added workbook with your vba code and sheet with my formula version. (http://sdrv.ms/17Xk3Ga )


regards,


Biray

 

[kocakb]

Hi Hui,


thanks for your post about scheduling in chandoo.org. it's also important source for me several years.


In my file, what i need was to have a formula which sums shifts by day. So, i cropped other parts which calculate how much each source. i added my formula to calculate this. you can see in following file. ( http://sdrv.ms/17Xk3Ga ) What i expect is a simpler or more strong formula.


regards,


Biray

 

[NARAYANK991]

Hi Biray ,


I am not sure whether this is what you are looking for ; if you wish to simplify the formulae you have used in cells DZ3 through FO10 , then can you try this in DZ3 ?


=SUM(OFFSET($B3,,MATCH(DZ$1,$B$1:$DW$1,0)-1,1,3))


Enter this as an array formula , using CTRL SHIFT ENTER ; copy it across and down.


Narayan

 

[kocakb]

Hello NARAYANK991,


That's it. your formula works perfect without array.


Thank you very much


regards,


Biray

 

[NARAYANK991]

Hi Biray ,


Sorry , my mistake ; you are right that the formula will work as a normal formula.


Narayan