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]

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?

Possibly two options.

1. For the layout of my previous post, change the C6 formula to this before copying across and down.
=IF(COUNTIF($B6:B6,LOOKUP(C$5-$B6,$C$3:$K$3,$C$1:$K$1)),"",LOOKUP(C$5-$B6,$C$3:$K$3,$C$1:$K$1))

2. Change how you pick this up on the other sheet to only pick up the first instance. If you are interested to see if that is feasible/easy give us an idea (screen shot) of what is on the other sheet and what formula(s) you are currently using there.

 

[CTMom]

I am pasting the 2 sheets. 1) shows manual inputs of stages and 2) shows the payments for each stage that picks up from the first sheet

Sheet 1

Excel Workbook
	A	B	D	E	F	G	H
7	Job	Contract Amount	5/11/2010	12/11/2010	19/11/2010	26/11/2010	3/12/2010
8	Nov	*	*	*	*	*	*
9	1430	* * *194,415	*	Site	*	*	Base
10	1432	* * *194,415	*	Site	*	*	Base
11	1433	* * *209,290	*	Site	*	*	Base
12	1844	* * *201,665	*	Site	*	*	Base
13	1935	* * *162,172	*	Site	*	*	Base
14	1418	* * *204,690	*	Site	*	*	Base
New jobs

Sheet 2

Excel Workbook
	A	B	C	D	E	F	G
4	Site	5%	*	*	*	*	*
5	Base	10%	*	*	*	*	*
6	Frame	15%	*	*	*	*	*
7	Lockup	35%	*	*	*	*	*
8	Fix	25%	*	*	*	*	*
9	PCI	10%	*	*	*	*	*
10	Job	Contract Value	12/11/2010	19/11/2010	26/11/2010	3/12/2010	10/12/2010
11	Nov	* * * * * * * * * *- *	*	*	*	*	*
12	1430	* * * * 194,415	* * * * * 9,721	* * * * * * * * *- *	* * * * * * * * *- *	* * * * 19,442	* * * * * * * * *- *
13	1432	* * * * 194,415	* * * * * 9,721	* * * * * * * * *- *	* * * * * * * * *- *	* * * * 19,442	* * * * * * * * *- *
14	1433	* * * * 209,290	* * * * 10,465	* * * * * * * * *- *	* * * * * * * * *- *	* * * * 20,929	* * * * * * * * *- *
15	1844	* * * * 201,665	* * * * 10,083	* * * * * * * * *- *	* * * * * * * * *- *	* * * * 20,167	* * * * * * * * *- *
16	1935	* * * * 162,172	* * * * * 8,109	* * * * * * * * *- *	* * * * * * * * *- *	* * * * 16,217	* * * * * * * * *- *
17	1418	* * * * 204,690	* * * * 10,235	* * * * * * * * *- *	* * * * * * * * *- *	* * * * 20,469	* * * * * * * * *- *
New jobs by amt

Any suggestions?

Thanks for all your help

 

[Peter_SSs]

First thing is a hint for Excel jeanie. You can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board.

For example, for 'New jobs' above we didn't need to see any of those formulas at all and for 'New jobs by amt' it would have been sufficient to just see the row 12 formulas since all the ones below that are just repeats. In fact we probably only needed the C12 formula since I think it is just copied across and down. Use the Test Here forum to experiment with restricting the formulas.

I assume you are asking how you might get the amounts on 'New job by amt' if using my suggestion in 'New jobs'. If so, I will use the same layout for 'New jobs' as I had for my previous screen shot, apart from the fact that I have
- inserted a new column B to accommodate the Contract Amount.
- Deleted the 5-Nov column since it wasn't being used and also isn't shown on your 'New jobs by amt' sheet.

So, my 'New jobs looks like this with the formula suggested in post #11 in D6 and copied across and down (note I haven't shown all the formulas in the Excel jeanie shot - just 1)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
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	Amount	Start Date	12-Nov	19-Nov	26-Nov	03-Dec	10-Dec	17-Dec	24-Dec	31-Dec	07-Jan	14-Jan
6	Job 1	194,415	7/11/10	Site		Base		Frame			Lockup
7	Job 2	194,415	11/11/10	Site		Base		Frame			Lockup
8	Job 3	209,290	20/11/10			Site		Base		Frame			Lockup
9	Job 4	201,665	26/11/10			Site		Base		Frame			Lockup
10	Job 5	162,172	8/11/10	Site		Base		Frame			Lockup
New jobs

Then in 'New jobs by amt' you could use the following.

A11 and B11 copied down as far as you want.
C11 copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
4	Site	5%
5	Base	10%
6	Frame	15%
7	Lockup	35%
8	Fix	25%
9	PCI	10%
10	Job	Value	12/11/2010	19/11/2010	26/11/2010	3/12/2010	10/12/2010	17/12/2010	24/12/2010	31/12/2010
11	Job 1	194,415	9,721		19,442		29,162			68,045
12	Job 2	194,415	9,721		19,442		29,162			68,045
13	Job 3	209,290			10,465		20,929		31,394
14	Job 4	201,665			10,083		20,167		30,250
15	Job 5	162,172	8,109		16,217		24,326			56,760
16
New jobs by amt

 

[CTMom]

Hi Peter

Thank you so much for this. This works perfectly. One question - why do you need the -1000 and 1 at either end of the Cum Duration row?

Thanks for all your help.

 

[Peter_SSs]

why do you need the -1000 and 1 at either end of the Cum Duration row?

That relates to the LOOKUP function. Let's just extract one part of the fomula:
LOOKUP(D$5-$C6,$D$3:$L$3,$D$1:$L$1)
LOOKUP searches for the red value, in the blue range and returns the corresponding value from the green range. For LOOKUP to work, the blue range must be sorted in ascending order. If lookup cannot find the red value it drops down to the next value lower than the red value.

Look at this part of the formula in D6. The red part is
D5 - C6 = 12 Nov - 7 Nov = 5
So LOOKUP looks for 5 in the blue range. 5 does not appear in the blue range so LOOKUP drops to the next value lower than 5. This is 0 and so LOOKUP returns the corresponding value from the green range, which is "Site"

Look at this part of the formula that has been copied down to D8
LOOKUP(D$5-$C8,$D$3:$L$3,$D$1:$L$1)
D5-C8 = 12 Nov - 20 Nov = -8
So LOOKUP looks for -8 in the blue range. -8 does not appear so it drops down to the next value lower than -8. This is -1000 so LOOKUP returns the corresponding value from the green range, which is "". So, if -1000 (or some similar value) wasn't there, the formula would error because it cannot find a number lower than -8.

I didn't do the other end of the range quite as efficiently but since it is working let's leave it as is. It works basically the same. If the 140 wasn't there, then when VLOOKUP was looking for a number >139 (this will occur when the row 5 date is > 139 days ahead of the start date) it wouldn't find that number so it would drop back to the next value lower which is 139 and would return "PCI". That would mean you would get "PCI" in all cells > 139 days after the Start Date.

Hope that made some sense. As is usual, it is easier to write a formula than explain it.