# Copy target value across based on cell value

#### Wardylewis

##### New Member
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.

 Week Start Time to complete in weeks number of actions weekly target value 3 5 60 10 12 2 4 2 7 15 48 3.2

<tbody>
</tbody>

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.

 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 2 2 2 2 2 2 2 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 3.2 3.2 0 0 2 2 2 2 5.5 5.5 3.2 3.2 3.2 5.5 5.5 3.2 3.2 3.2 3.2 3.2 3.2 3.2 3.2 3.2

<tbody>
</tbody>

I am hoping some one can assist.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Eric W

##### MrExcel MVP
Something like this?

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

##### New Member
Something like this?

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

##### MrExcel MVP
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.