what column is the X in ?

[Paula F]

I am trying to improve production plans in Excel (company is currently forcing us to use microsoft project which is just far too complicated and not the right tool in my opinion) Basically I would have product codes in one column, and then qty to make, sales order date, and hours it take to make it / or number of shifts (whihc I can do a formula to get form the standard hours). I then have days split into shifts across the top of the page (so one column for each shift). I then put a X in the box of the shift when the product is planned to be made. I want to be able to show what the start and finish dates will be for each order. (this is easy in Project), then I can compare finish dates with sales order date to say whihc ones will be late.
Would it be possible to say "look for an X and tell me which date is at the top of the column" ? but there would be more then one X sometimes (if production is for more then one shift), and I would want the start and finish date. Can anyone help ? I would be very grateful !!!!
DOes anyone show production plans in Excel any other way ?
thank you in advance
Paula

 

[onlyadrafter]

Hello and welcome to the board,

Can you not supply some sample data or download your spreadsheet, as this would be much easier to understand.

 

[Paula F]

HIYA
I have created an example of what I want, but how do I post it ? (not good I know if I cant even do that !)

thanks

Paula

 

[onlyadrafter]

Hello,

Scroll down this page and there is a link to Colo's HTLM maker.
Try it on the test area, if you can't do this, then you will have to give a fuller description.

 

[just_jon]

Book2
	A	B	C	D	E	F	G	H	I	J
1		Start	Stop	11/10/2005	11/11/2005	11/12/2005	11/13/2005	11/14/2005	11/15/2005	11/16/2005
2	task1	11/11/2005	11/15/2005		X			X	X
3	task2	11/13/2005	11/14/2005				X	X
4
Sheet1

 

[Paula F]

Dear Jon

thank you so much !! that is amazing !
please could you explain the formula to me, and why and how it is different to get the end date.
I'm so pleased I found this site.
Paula

 

[Paula F]

I don't suppose there is any chance of being able to automatically move all the X's along ? eg if there is a delay in production and they all need to move out by a day...it then means deleting all the X's and starting again. I want to have rolling plans for months and months, so it would be impossible to delete all of it once there is a delay.
thanks
Paula

 

[just_jon]

Dear Jon

thank you so much !! that is amazing !
please could you explain the formula to me, and why and how it is different to get the end date.
I'm so pleased I found this site.
Paula

MATCH with a 3rd argument of 0 will find the 1st instance of the target.

MATCH with the 3rd argument omitted will effectively find the last instance of the target.

When MATCH is fed as an argument to INDEX, it is used to denote the nth value to return from the indexed array.

 

[just_jon]

I don't suppose there is any chance of being able to automatically move all the X's along ? eg if there is a delay in production and they all need to move out by a day...it then means deleting all the X's and starting again. I want to have rolling plans for months and months, so it would be impossible to delete all of it once there is a delay.
thanks
Paula

Well, you might be able to use a double-click code event to bump them out 1 period each time, I guess.

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
<SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    i = .Match("X", Range(Cells(Target.Row, "D"), Cells(Target.Row, "IU")), 0) + 3 <SPAN style="color:#007F00">' position of first X</SPAN>
    j = .Match("X", Range(Cells(Target.Row, "D"), Cells(Target.Row, "IU"))) + 3 <SPAN style="color:#007F00">' position of last X</SPAN>
    Range(Cells(Target.Row, i), Cells(Target.Row, j)).Copy Cells(Target.Row, i).Offset(0, 1)
    Cells(Target.Row, i).ClearContents
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Cancel = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeRightClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
<SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    i = .Match("X", Range(Cells(Target.Row, "E"), Cells(Target.Row, "IU")), 0) + 4 <SPAN style="color:#007F00">' position of first X</SPAN>
    j = .Match("X", Range(Cells(Target.Row, "E"), Cells(Target.Row, "IU"))) + 4 <SPAN style="color:#007F00">' position of last X</SPAN>
    Range(Cells(Target.Row, i), Cells(Target.Row, j)).Copy Cells(Target.Row, i).Offset(0, -1)
    Cells(Target.Row, j).ClearContents
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Cancel = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

The above code would go into the worksheet module of the sheet holding the Xs.

It should bump to the right your X range when you double-click a row, and move to the left when you right-click a row.

The above code has had very limited testing.