Allocate Values from a range into columns under corresponding Months

  • Hello


    I have a table with Unit Numbers in Column A (24A, 24B, 25A, 25B and so on) , Sale Month in Column B, Price in Column C and Stagewise Price Breakup (10 Stages) in Columns D to M.


    Price Breakup is pre-defined (Like monthly installments) based on certain percentage applied to each stage and that payment becomes due when that particular stage is completed. These stagewise payments are not equal.


    Time frame for each stage is also pre-defined i.e. if "Stage1" i.e. "Sale" happens in "Month1", then stage2 comes in Month2, Stage3 comes in Month4 and so on. There are 2 month gaps between certain stages. Once the Sale of specific Unit is made, then a specific month (For example Mar-17) will be applied as start month (For stage1) for that unit.


    I have another sheet which looks like a running calender, which has Unit Numbers in Column A (same as the previous Data sheet) and Column B onwards have Months in the header row i.e. Feb-17, Mar-17, Apr-17 and so on till year 2020.


    I am trying to figure out a way to automatically allocate values in the Data table to the Calender under specific months, in the row for the specific Unit No.


    For example, if Unit 25B is sold in Mar-17, then I will enter "start Month" or "Stage1" as Mar-17 and the following stagewise Values (which are already pre-defined in the table) should be allocated in the calendar under correct month i.e. Stage2 value should fall under Apr-17, Stage3 Value should fall under Jun-17 and so on.


    Can someone help please ! I would really appreciate it !


    Thanks
    V

  • Re: Allocate Values from a range into columns under corresponding Months


    Hi V


    Try this Code in the attached. Open the File to Sheet "CashFlow". Ctrl + x will file the Code.

  • Re: Allocate Values from a range into columns under corresponding Months


    Hello


    Thanks jaslake.....it works brilliantly, however there are two issues.


    1) All the production stages do not fall in consecutive months...for example. Stage2 is in Month2, but Stage3 falls in Month4. (there is a 2 month gap between stage2 & stage3). So if Stage2 Falls under Mar-17, then Stage3 will need to fall under May-17.


    2) Stage9 and Stage10 , both fall under Month10. So, combined value of Stage9 & stage10 needs to be shown in the same Month i.e. Month10.


    I really appreciate your help in this.


    Actually, Initially to make it more professional looking & user friendly, I was trying to see if this could also be achieved with the help of Combobox and command button on the worksheet "Cashflow", where Combobox1 will show the list of Units, Combobox2 will show the List of Months (to select the Sale Month or start Month or Month1) and rest of the process will remain the same where the based on the Month1, remaining stage values will fall in corresponding months.


    I am not sure how difficult or complicated this could get, but just a thought. If this is too complex and time consuming, then I would appreciate if you could just help me with the above two issues.


    Thanks a ton again


    Cheers
    V

  • Re: Allocate Values from a range into columns under corresponding Months


    Hello


    I am trying to insert another set of exactly identical tables for expense side in Worksheet "Cashflow" and Worksheet "PS". I mean exactly identical table from rows 32 to 53 with similar headings in Worksheet "PS" and rows 34 to 57 in worksheet "Cashflow". These second set of tables have expense / cost amount for each stage instead of the billing amount.


    So, I am trying to build something where when I enter sale month for a Unit number, then it will add billing values (from first table) under specific months and will also enter the expense value in second table from expense table for that unit.


    I tried copy - pasting your formula in another worksheet, but It gives me error highlighting "j = myCol.Column" ..... I am obviously doing something wrong while replicating the formula..., but am unable to identify it


    Can you please help ! Thanks a lot.

  • Re: Allocate Values from a range into columns under corresponding Months


    Hi jaslake.......Ok...Thanks for letting me know. Will wait for your suggestions.

  • Re: Allocate Values from a range into columns under corresponding Months


    Hi V


    This Code in the attached appears to resolve this...

    Quote

    1) All the production stages do not fall in consecutive months...for example. Stage2 is in Month2, but Stage3 falls in Month4. (there is a 2 month gap between stage2 & stage3). So if Stage2 Falls under Mar-17, then Stage3 will need to fall under May-17.


    2) Stage9 and Stage10 , both fall under Month10. So, combined value of Stage9 & stage10 needs to be shown in the same Month i.e. Month10.



    The other "Wants" should probably be the subject of a new Thread. You may PM a Link to your new Thread if you wish.

  • Re: Allocate Values from a range into columns under corresponding Months


    Hello jaslake, sorry to bother you again.


    Without changing anything (structure or setup) I tried to link the "Price" column in sheet "PS" to another sheet price calculation sheet I added and its giving me error now. Its giving "Run-time error 91" .


    Could you please help ! I would really appreciate.


    I am attaching the modified file for your reference alongwith the screenshots for the error.


    Thanks again !

  • Re: Allocate Values from a range into columns under corresponding Months


    Hello.....I tried this again on another copy of the file and the above mentioned last problem seems to have gone away.....Sorry for the trouble....Thanks again

  • Re: Allocate Values from a range into columns under corresponding Months


    Got it....the Sale date format in PS sheet....I fixed it in the same old sheet and that worked too....thanks a lot for all your help and patience.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!