Copy target value across based on cell value

[Wardylewis]

Good Evening All,

I don't know if this is possible and am really struggling with this problem.

We have a sheet which lists how many actions are needed to be done and what week they will start closing them out followed by how many weeks it will take them. This way we can give them a target weekly value. I am now just struggling how to pull this data across for a graph.

So on our data input sheet it looks like this.

[TABLE="width: 100"]
<tbody>[TR]
[TD]Week Start[/TD]
[TD]Time to complete in weeks[/TD]
[TD]number of actions[/TD]
[TD]weekly target value[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5
[/TD]
[TD]60[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]15[/TD]
[TD]48[/TD]
[TD]3.2[/TD]
[/TR]
</tbody>[/TABLE]

What I am then looking for is for it to make a plan of the weeks to show how many actions must be completed in that given week by using the target values worked out above.

[TABLE="width: 100"]
<tbody>[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]5.5[/TD]
[TD]5.5[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]5.5[/TD]
[TD]5.5[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[TD]3.2[/TD]
[/TR]
</tbody>[/TABLE]

I am hoping some one can assist.

Thank you in advance.

 

[Eric W]

Something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Project	Week Start	Time to complete in weeks	number of actions	weekly target value
2	1	3	6	60	10
3	2	12	2	4	2
4	3	7	15	48	3.2
5
6
7	Project	Week: -->
8		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22
9	1			10	10	10	10	10	10
10	2												2	2
11	3							3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2
12
13		0	0	10	10	10	10	13.2	13.2	3.2	3.2	3.2	5.2	5.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	0
Sheet5

Cell Formulas
Range	Formula
E2	=D2/C2
B9	=IF(OR(B$8<$B2,B$8>$B2+$C2-1),"",$E2)
B13	=SUM(B9:B11)

Drag the E2 formula down. Copy the B9 formula across and down as needed. Drage the B13 to the right. There are variations of this in which we could match by project number instead of just going down the list, or by putting the result table on another page.

Let us know how this works.

 

[Wardylewis]

Something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Project	Week Start	Time to complete in weeks	number of actions	weekly target value
2	1	3	6	60	10
3	2	12	2	4	2
4	3	7	15	48	3.2
5
6
7	Project	Week: -->
8		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22
9	1			10	10	10	10	10	10
10	2												2	2
11	3							3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2
12
13		0	0	10	10	10	10	13.2	13.2	3.2	3.2	3.2	5.2	5.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	3.2	0
Sheet5

Cell Formulas
Range	Formula
E2	=D2/C2
B9	=IF(OR(B$8<$B2,B$8>$B2+$C2-1),"",$E2)
B13	=SUM(B9:B11)

Drag the E2 formula down. Copy the B9 formula across and down as needed. Drage the B13 to the right. There are variations of this in which we could match by project number instead of just going down the list, or by putting the result table on another page.

Let us know how this works.

Hi Eric,

That has worked an absolute treat thank you, I believed it worked around an if function but I was using and rather than or.

Could you please explain how it’s doing it so I know the logic and know for next time. It’s the bit around $C2-1.

Thank you

 

[Eric W]

Sure.

=IF(OR(B$8<$B2,B$8>$B2+$C2-1),"",$E2)

The value in red is the week #, which it gets from row 8. That's why the $ is used. The value in blue is the start week from $B2. As you drag the formula down, it becomes $B3, $B4, etc., so it changes to the next projects. So the first part of the OR says, "If the week on this column is less than (before) the project start week". $C2 in orange is the number of weeks. So $B2+$C2-1 is the last week of the project. Thus the second part of the OR says, "If the week on the column is greater than (after) the project end week". If either of those conditions are true, display "". Otherwise, display the number of actions ($E2 in violet).

Incidentally, you could do it with an AND instead of the OR if you rearrange a few things.