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.

Priority	Die #	Length of run	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
1	110	8	x	x	x	x	x	x	x	x
2	115	2									x	x
3	125	14											x	x	x	x	x	x	x	x	x	x	x	x	x	x
4	095	6

<tbody>
</tbody>

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