Lookup possibly?

[CTMom]

I have a spreadsheet with information about construction stages. See below for example:

Weekly schedule: Nov-5 Nov-12 Nov-19 Nov 26
Job Start Base
Job Start Base

I have a long list of jobs and want to automatically insert the next stage based on the Start date. For example the Base stage comes automatically 2 weeks after Start date. There are 5 stages and the duration between each stage is the same for each job.
Is there a way I can automate the filling in of the spreadsheet so that if the Start date changes it automatically updates the rest of the row with the other stages.

Any help would be greatly appreciated.
Thanks

 

[Peter_SSs]

I'm a bit unsure about your layout and data and whether a job can start on a day other than one of your headings (see rows 5 & 6 below), but see if you could use something like this.

Formula in E3 is copied down.
Formula in F3 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1						1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
2	Job	Start Date	Stage duration	No. Stages	Start Col	5-Nov	12-Nov	19-Nov	26-Nov	3-Dec	10-Dec	17-Dec	24-Dec	31-Dec	7-Jan	14-Jan	21-Jan	28-Jan	4-Feb	11-Feb
3	Job1	12-Nov	3	5	2		Start			Base			Base			Base			Base
4	Job2	19-Nov	2	4	3			Start		Base		Base		Base
5	Job3	28-Nov	1	6	4				Start	Base	Base	Base	Base	Base
6	Job4	10-Nov	4	3	1	Start				Base				Base
7
Job Dates

 

[CTMom]

Hi thank you for your reply. Not sure if this will work because there are 5 construction stages for building the house and every house has the same number of stages and every stage has differing durations. Eg. the duration from start date to base is 21 days, from base to frame is 14 days, etc. So everything would probably work well with your formula except for the Stage Duration column as each of the 5 stages has different duration.

Any thoughts would be greatly appreciated.

 

[Peter_SSs]

Could you give the stage durations of every stage and post some sample data (say 5 sets) and results (entered manually) so we can see just how it should turn out?

 

[CTMom]

Hi Peter, thanks for reply. I do not know how to paste excel file into this message so this is manual version:

6 Stages and the number of days from one stage to next:
Site - day 0
Base - 21 days from Site
Frame - 14 days from Base
Lockup - 30 days from Frame
Fix - 30 days from Lockup
PCI - 30 days from Fix

So the table would be like this:

week ending: 5 Nov 12 Nov 19 Nov 26 Nov 3 Dec 10 Dec
Job 1 Site Base Frame
Job 2 Site Base

I want to insert a column next to the Job number with the date that the job starts (ie. Site Start date) and then have the formula populate the rest of the row with the correct stage based on the number of days from the previous stage.

Is there anything that you think can achieve this? Would be very grateful for any help.

Thanks very much

 

[CTMom]

I just posted reply with a table but the spaces did not work so all the stages are bunched together instead of spread out across the dates. I hope this can still make sense?????

 

[Peter_SSs]

I just posted reply with a table but the spaces did not work ..

Investigate these. I have used the first one in my earlier post and you can obviously see how much clearer it is where things are on the sheet and the data can also be copied from such a screen shot to a test file if required.

Excel jeanie
or
RichardSchollar’s beta HTML Maker - see the red text in his signature block at the bottom of his post
or
Borders-Copy-Paste

Before investing too much more time, I would very much like to see just how the table will look. In particular, how you envisage having weekly headings across the top but stages (and presumably start dates) that don't necessarily align with the week start dates.

 

[CTMom]

Here is the sample table. The start dates just show the day that construction commences and I am only interested in the week that they occur in. I would like the rows across (row 6 for example) to be able to look up to the week for each column and then insert the correct Stage name (or it could be stage number) in the right week based on the start date.
I hope this makes it clearer.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Stage	*	*	Site	Base	Frame	Lockup	Fix	PCI	*	*
2	Days Duration From Previous Stage	*	*	0	14	14	21	30	30	*	*
3	*	*	*	*	*	*	*	*	*	*	*
4	*	*	5/11/2010	12/11/2010	19/11/2010	26/11/2010	3/12/2010	10/12/2010	17/12/2010	24/12/2010	31/12/2010
5	*	Site Start	*	*	*	*	*	*	*	*	*
6	Job 1	7/11/10	Site	*	Base	*	Frame	*	*	Lockup	*
7	Job 2	11/11/10	*	Site	*	Base	*	Frame	*	*	Lockup
8	Job 3	20/11/10	*	*	*	Site	*	Base	*	*	Frame
9	Job 4	26/11/10	*	*	*	Site	*	Base	*	*	Frame
10	Job 5	8/11/10	Site	*	Base	*	Frame	*	*	Lockup	*
Sheet1

 

[Peter_SSs]

I'm not exactly sure how you are calculating some of those stage dates. For example, your dates across the top are Friday dates so I would have thought they represented the end of the week.

So your Job1 which starts on 7 Nov to me starts after the end of the week so should be included as a start in the second column, not the first. It will certainly be easier if the top dates are clearly either the first day of each of your 'weeks' of the last day of each week. You seem to have used my logic for Job 3 but not Job 1 or Job 5.

Would this suffice?

Note the formulas in C1 and K1. (You could use a space character in these two cells instaed of that formula if you want, just don't leave them completely blank)

Note also the extra manual values in C3 and K2.

Formula in D3 is copied across.

Formula in C6 is copied right across your date columns and then right down your job rows.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		Stage		Site	Base	Frame	Lockup	Fix	PCI	PCI
2		Duration		0	14	14	21	30	30	30	1
3		Cum Duration	-1000	0	14	28	49	79	109	139	140
4
5	Job	Start Date	05-Nov	12-Nov	19-Nov	26-Nov	03-Dec	10-Dec	17-Dec	24-Dec	31-Dec	07-Jan	14-Jan	21-Jan	28-Jan	04-Feb	11-Feb	18-Feb	25-Feb	04-Mar	11-Mar	18-Mar	25-Mar	01-Apr	08-Apr	15-Apr
6	Job 1	7/11/10		Site	Site	Base	Base	Frame	Frame	Frame	Lockup	Lockup	Lockup	Lockup	Fix	Fix	Fix	Fix	PCI	PCI	PCI	PCI	PCI
7	Job 2	11/11/10		Site	Site	Base	Base	Frame	Frame	Frame	Lockup	Lockup	Lockup	Lockup	Lockup	Fix	Fix	Fix	Fix	PCI	PCI	PCI	PCI
8	Job 3	20/11/10				Site	Site	Base	Base	Frame	Frame	Frame	Lockup	Lockup	Lockup	Lockup	Fix	Fix	Fix	Fix	PCI	PCI	PCI	PCI	PCI
9	Job 4	26/11/10				Site	Site	Base	Base	Frame	Frame	Frame	Lockup	Lockup	Lockup	Lockup	Lockup	Fix	Fix	Fix	Fix	PCI	PCI	PCI	PCI
10	Job 5	8/11/10		Site	Site	Base	Base	Frame	Frame	Frame	Lockup	Lockup	Lockup	Lockup	Fix	Fix	Fix	Fix	PCI	PCI	PCI	PCI	PCI
Job Dates

Now if you really don't want the stages repeated like that, you could leave all the formulas as above then select from C6 to the bottom right of the data and apply the Conditional Formatting shown below. The format applied is white text.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1		Stage		Site	Base	Frame	Lockup	Fix	PCI	PCI
2		Duration		0	14	14	21	30	30	30	1
3		Cum Duration	-1000	0	14	28	49	79	109	139	140
4
5	Job	Start Date	05-Nov	12-Nov	19-Nov	26-Nov	03-Dec	10-Dec	17-Dec	24-Dec	31-Dec	07-Jan
6	Job 1	7/11/10		Site	Site	Base	Base	Frame	Frame	Frame	Lockup	Lockup
7	Job 2	11/11/10		Site	Site	Base	Base	Frame	Frame	Frame	Lockup	Lockup
8	Job 3	20/11/10				Site	Site	Base	Base	Frame	Frame	Frame
9	Job 4	26/11/10				Site	Site	Base	Base	Frame	Frame	Frame
10	Job 5	8/11/10		Site	Site	Base	Base	Frame	Frame	Frame	Lockup	Lockup
Job Dates

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6	1. / Formula is =C6=B6	Abc

 

[CTMom]

Thanks Peter. That works great. Any way of not repeating the stage once it has been used in previous cell. I pick up these stages on another spreadsheet for calculating payments and I only have one payment per stage so only need each stage occurring once. What is the best way to achieve this?