Show Widgets needed?

M

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hi,

Struggling to put something robust together to do this so looking for some assistance...

I've tried to illustrate what I'm trying to do below:

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 79px;"><col style="width: 102px;"><col style="width: 20px;"><col style="width: 90px;"><col style="width: 80px;"><col style="width: 79px;"><col style="width: 79px;"><col style="width: 79px;"><col style="width: 79px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Widget Date</td><td>Widget Quantity</td><td>
</td><td>Week Number</td><td>Requirement</td><td>Widget Date</td><td>Widget Date</td><td>Widget Date</td><td>Widget Date</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="text-align: right;">03/01/2011</td><td style="text-align: right;">100</td><td>
</td><td>Week 1</td><td style="text-align: right;">50</td><td style="text-align: right;">03/01/2011</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: right;">10/01/2011</td><td style="text-align: right;">100</td><td>
</td><td>Week 2</td><td style="text-align: right;">25</td><td style="text-align: right;">03/01/2011</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: right;">17/01/2011</td><td style="text-align: right;">100</td><td>
</td><td>Week 3</td><td style="text-align: right;">75</td><td style="text-align: right;">03/01/2011</td><td style="text-align: right;">10/01/2011</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: right;">24/01/2011</td><td style="text-align: right;">100</td><td>
</td><td>Week 4</td><td style="text-align: right;">150</td><td style="text-align: right;">10/01/2011</td><td style="text-align: right;">17/01/2011</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: right;">31/01/2011</td><td style="text-align: right;">100</td><td>
</td><td>Week 5</td><td style="text-align: right;">175</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: right;">07/02/2011</td><td style="text-align: right;">100</td><td>
</td><td>Week 6</td><td style="text-align: right;">25</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="text-align: right;">14/02/2011</td><td style="text-align: right;">100</td><td>
</td><td>Week 7</td><td style="text-align: right;">5</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="text-align: right;">21/02/2011</td><td style="text-align: right;">100</td><td>
</td><td>Week 8</td><td style="text-align: right;">75</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
Column A shows the widget creation date and Column B shows the quantity created. Column D shows the week number and Column E shows the widget requirement per week.

In Column F and beyond I would like to show the creation date of the widgets used to satisfy the requirement. So, week 1's requirement is 50, therefore it will use half of the widgets created on the 03/01/2011. Week 2's requirement is 25, so it will use a further 25 of the widgets created on the 03/01/2011. When we get to week 3, however, this will use the remaining 25 of widgets created on the 03/01/2011 and 50 of the widgets created on the 10/01/2011. And so on...

Does it make sense what I'm trying to do here? Been playing with this for a while but can't quite get it working.

Hope someone can help!

Thanks,

Matty
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
I've still not managed to work this out, so bumping it hoping someone can point me in the right direction.

Thanks,

Matty
 
Upvote 0
Matty,

I don't know if you've had a chance to look at this, but I posted an update. Same link.
 
Upvote 0
That's perfect, shg. Just what I was after.

Many thanks for spending the time to look at this for me.

Cheers,

Matty
 
Upvote 0
You're welcome, I'm glad it worked for you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
619
smozgur
smozgur
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
89
Dazzybeeguy
D
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
103
flashgordie
F
E
  • Question Question
VBA code clean up
Replies
9
Views
574
mumps
mumps
D
  • Solved
Re-distributing numbers against a total value.
Replies
6
Views
250
DazCD
D

Forum statistics

Threads
1,224,548
Messages
6,179,451
Members
452,915
Latest member
hannnahheileen

Share this page

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