Copy target value across based on cell value

Wardylewis

New Member
Joined
Jun 7, 2016
Messages
37
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 StartTime to complete in weeksnumber of actionsweekly target value
35
6010
12242
715483.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.

12345678910111213141516171819202122
222222
22
3.23.23.23.23.23.23.23.23.23.23.23.23.23.23.23.2
0022225.55.53.23.23.25.55.53.23.23.23.23.23.23.23.23.2

<tbody>
</tbody>

I am hoping some one can assist.

Thank you in advance.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Something like this?


Book1
ABCDEFGHIJKLMNOPQRSTUVW
1ProjectWeek StartTime to complete in weeksnumber of actionsweekly target value
21366010
3212242
43715483.2
5
6
7ProjectWeek: -->
812345678910111213141516171819202122
91 101010101010
10222
1133.23.23.23.23.23.23.23.23.23.23.23.23.23.23.2
12
13001010101013.213.23.23.23.25.25.23.23.23.23.23.23.23.23.20
Sheet5
Cell Formulas
RangeFormula
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.
 
Upvote 0
Something like this?


Book1
ABCDEFGHIJKLMNOPQRSTUVW
1ProjectWeek StartTime to complete in weeksnumber of actionsweekly target value
21366010
3212242
43715483.2
5
6
7ProjectWeek: -->
812345678910111213141516171819202122
91 101010101010
10222
1133.23.23.23.23.23.23.23.23.23.23.23.23.23.23.2
12
13001010101013.213.23.23.23.25.25.23.23.23.23.23.23.23.23.20
Sheet5
Cell Formulas
RangeFormula
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top