How to offset range based on date?

[tg7384]

Hoping someone out there can help me.

Basically, what i would like to be able to do is the following:-

I have a quantity (in cell E5) and ship date (in cell S5) and a range of months/years in columns V onwards.

What i would like to do is to populate the quantity under the corresponding month/year column (row 5), depending on the ship date. In addition, i would like to distribute the quantity based on specific %'s.

Example

Quantity = 50,000
Ship Date = 15th May 2014

Month 1 = 50% of qty
Month 2 = 0% of qty
Month 3 = 0% of qty
Month 4 = 20% of qty
Month 5 = 3.75% of qty
Month 6 = 3.75% of qty
Month 7 = 3.75% of qty
Month 8 = 3.75% of qty
Month 9 = 3.75% of qty
Month 10 = 3.75% of qty
Month 11 = 3.75% of qty
Month 12 = 3.75% of qty

Data should populate as follows:-

04/14,05/14,06/14,07/14,08/14,09/14,10/14,11/14,12/14,1/15,2/15,3/15,4/15,5/15,6/15
0,25000,0,0,1875,1875,1875,1875,1875,1875,1875,1875

Of course, there will be a number of rows that need evaluating with the same distribution.

Any help would be welcome. Thanks!
James

[Kaper]

Why macro? simple (relatively ;-) ) formula would do.
In I12:

Please Login or Register  to view this content.

and copy down/right

it checks if it is already after start and if yes - before dozen months and if both yes then takes (INDEX) data from I4:T4 from first (only) row and appropriate (month difference) column.

[tg7384]

Kaper - brilliant! What would we do without people like you!

Only one small typo, but i can deal with that - thanks so much!

One other quick question, is there an easy way to sum the total cost for each month in cell I8...?8, for all entries beneath, taking the respective volume /individual price (column E) for each item?

Makes sense?

Once again - simple, but brilliant! Thanks!

James

[Kaper]

Please Login or Register  to view this content.

[tg7384]

Of course! Thanks for all your help!!!!!

[Kaper]

Glad to hear that. And thanks for reputation point

[tg7384]

Kaper,
One last question....

What if i wanted to apply different % models, depending on the product type?

ie: there could be 5 % models.

Let me know?

Thank

[Kaper]

instead of

Please Login or Register  to view this content.

use something like

Please Login or Register  to view this content.

or already in

Please Login or Register  to view this content.

put

Please Login or Register  to view this content.

[tg7384]

Thanks mate. I take it that "model" is whatever the cell value is we need to lookup, and the "listofmodels" is a range of the model names next to the % distribution? Thanks again!


Sent from my iPad using Tapatalk