I have a spreadsheet that has following:
a) Product Payment Schedule that has Unit Number (20A, 20B, 21A,21B, and so on) in the Header Row and Payment stages in the column A i.e.
Stage 1 - 35% (Of the total cost),
Stage 2 - 30%(Of the total cost),
Stage 3 - 20% (of the total cost),
Stage 4 - 15% (Of the total cost)
Each stage requires specific time gap before it goes to the next stage. i.e. If Stage1 is Month1, then stage 2 will come in Month3, stage3 will come in Month6 and Stage4 in Month8 when the product will be delivered.
Depending upon the the Unit Sale Price entered, it applies the percentage and splits the stagewise payments for each Unit (in their specific columns and in corresponding rows showing the stage number in left column)
I am trying to build a cash flow and hence trying to export this information into another worksheet named "Unitwise Cashflow" where the Header Row contains Month/Year i.e. Feb-17, Mar-17, Apr-17 and so on till Feb-20 (B5:AL5) and column A contains Unit numbers (A6:A29).
I am trying to automate this part by having the Unit numbers displayed in Combobox1 and Month/year list displayed in combobox 2 as the start date for that unit. And the following stage amounts (from other worksheet) should fall under respective months automatically.
For example, If I select Unit "21B" from the Unit List (Combobox) and Select "May-17" from Combobox2 as Start Date for the Unit, then Amount for the Stage1 will fall in a cell that corresponds to the Rows that has 21B in column A and column that has "May-17". As the time duration between each stage is fixed, once I set the starting Month, can I have the corresponding values from the following stages i.e. Stage2, stage3 and Stage4 fall into respective Month/Year Columns.
I would appreciate if someone could help. I am unsure of what this functionality is called and what I should be searching for.
I have attached a test file to try and explain what I am trying to achieve., Thanks