Offset start??

[kod2th3e]

I am trying to place an "x" in each cell for the corresponding hour based on the length of run. Then when a run is complete, I would like an "x" for the next scheduled item to show in the next available hour on the preceding row and so on up to 24 hours. How to I offset the next row to start at the following hour? Below is what I'm trying to accomplish but I'd like to use a formula to place the "x" in the cell rather than having to manually place them. Any/all help is greatly appreciated, thank you for looking.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Priority[/TD]
[TD]Die #[/TD]
[TD]Length of run[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]110[/TD]
[TD]8[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]115[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]125[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]095[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In this example die #095 wouldn't have any "X's" in any cell because all of the hours have been spoken for. Hopefully this helps to clarify the intent, thanks again for any help.

 

[James006]

Hi,

In cell D2, you could test following

=IF(COUNTIF(D$1:D1,"x")>=1,"",IF(COLUMN()-3<=SUM($C$2:$C2),"x",""))

HTH

 

[kod2th3e]

James006,

Thanks for your response. When I put the formula in, it doesn't appear to count all 8 hours of the run length. It only places an "x" in the first 3 columns. The other rows seem to work fine though, any idea what I did wrong?

 

[kod2th3e]

James,

Nevermind my question below, I added a couple of columns and realized that I needed to change the -3 to -8 because I had added an additional 5 columns between the beginning of the sheet and where the formula is. This worked perfectly, thank you for your help.

James006,

Thanks for your response. When I put the formula in, it doesn't appear to count all 8 hours of the run length. It only places an "x" in the first 3 columns. The other rows seem to work fine though, any idea what I did wrong?

 

[James006]

James,

Nevermind my question below, I added a couple of columns and realized that I needed to change the -3 to -8 because I had added an additional 5 columns between the beginning of the sheet and where the formula is. This worked perfectly, thank you for your help.

Glad you could solve your problem ...

You are welcome

 

[kod2th3e]

James,

One last question.. How could I offset the next row by an additional hour? So in the example above it shows die #115 starting in hour 9, how could I get it to start in hour 10 instead?

 

[James006]

Hello,

Is it a generic rule change ... or do you need the flexibility to offset project by project ( i.e. for each row : Y or N ) ?

 

[kod2th3e]

Hello,

Is it a generic rule change ... or do you need the flexibility to offset project by project ( i.e. for each row : Y or N ) ?

I believe it'd be a generic rule change, I'd like every preceding row to start one hour after the previous. In my previous post I indicated that die #115 should start in hour 10, I'd like die #125 to start with a 1 hour gap from the previous die (#115 ) which would make it start at hour 13. Would that be a generic rule change? If so, that's what I'm trying to accomplish.

 

[James006]

Hello,

For you new rule ...

You can test in cell D2 the following formula:

=IF(OR(COUNTIF(D$1:D1,"x")>=1,COUNTIF(C$1:C1,"x")>=1),"",IF(COLUMN()-(ROW()+1)<=SUM($C$2:$C2),"x",""))

Hope this will help

 

[kod2th3e]

Hello,

For you new rule ...

You can test in cell D2 the following formula:

=IF(OR(COUNTIF(D$1:D1,"x")>=1,COUNTIF(C$1:C1,"x")>=1),"",IF(COLUMN()-(ROW()+1)<=SUM($C$2:$C2),"x",""))

Hope this will help

James,

Worked good. I have a lot more rows of data than what was originally displayed so after the last die on the list that is scheduled all of the preceding dies would place an x in every other row/column. I was able to fix this by adding an "IF" statement to your formula that looks at the "length of run" column and if it's less than 1 it will make the cell blank ("") otherwise if it's greater than 1 it'll evaluate the formula you provided. You were such a great help, thank you for your time and support.

Cheers,
-Cody