Posts by vikrampnz

    Re: Automating Relative entries into the corresponding cells


    Hello.... I have the same name range "SaleMonth" that is created in the header row displaying months.... That is populated in the combobox using the code you gave earlier....


    The months are displayed in header row in "mmm-yy" format, but when I select a header cell it shows the entire date i.e. normally a cell would show Jun-17, however when selected it shows 01/06/2017 in the upper information bar .... I am not sure how to get it to look like month-year format in the upper cell content display space...


    At my end, Even in your original file (which you sent), dates are displayed in the same format in drop down (01/06/2017).....


    Is there a way I can change date format?


    Thanks for your help and patience

    Re: Automating Relative entries into the corresponding cells


    When hover the mouse over the code, on "Dim findRow As Range, findMonth As Range, findRateInfo As Range", it shows correct values from the combobox selection on "findrow As Range" and "findRateInfo as Range", but when I hover over "findMonth As Range", it says "findMonth = Nothing" ...... does this help identify the possible problem at my end?


    I tried reading through Google search on runtime error 91, some people are talking about Registry cleaning for solving this problem......do you think it could be this by any chance?


    Thanks a lot for your patience and help Bryce.....and sorry for too many questions....but I am really desperate to make this work.


    Awaiting your feedback..

    Re: Automating Relative entries into the corresponding cells


    Hi Bryce.....I wonder if this has anything to do with the version of excel I am using? I am using Excel 2007. I pasted the code in another sheet and its giving me "Run-time error 91". It says "findMonth.Column= <Obejct Variable or With Block Variable not set>" ...... Could you please help....I have attached the screenshots for your reference...thanks

    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: Automating Relative entries into the corresponding cells


    Hi Bryce....I have downloaded your file and was trying the same. I have uploaded the same here again. (which is your file)


    What I notice is that 1) after every entry, the Month dropdown defaults to Jul-17, 2) For some months it is giving Run-time error "91" ...."Object Variable not set"


    I am not sure why you are unable to replicate the problem at your end. In the attached file, when I enter a sale month, it only falls under Jul-17. It is picking correct values, entering them at correct intervals......but only starts from Jul-17.


    Thanks a lot for your help !

    Re: Automating Relative entries into the corresponding cells


    Hi Bryce


    Irrespective of what Month I select from the drop down, it seems to insert the Stage1 Value under Jul-17. Its picking the correct values for the selected Unit no, but just placing them under wrong Months.


    I really appreciate your help !


    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: Automating Relative entries into the corresponding cells


    Hello


    Sorry for the late reply.


    I tried the sample sheet you have attached. It seems to work, but for some reason it only starts with SaleMonth as July 17. If I select any other month, it is giving error.


    Could you please help !


    Thanks again for your response.


    Cheers
    V

    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

    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

    Hello


    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.


    Thanks again


    V


    I have attached a test file to try and explain what I am trying to achieve., Thanks

    Hello



    I was struggling to think what should be the subject of this post as I am unsure what this function/process is called. Anyway, my apologies if the subject looks confusing.



    Here's what I am trying to achieve:



    I have a spreadsheet with a list of people. They are all paid quarterly (three-monthly), however their quarters are different depending upon the date they join. For some people, payments are made Jan-Apr-Jul-Dec for some it is Feb-May-Aug-Jan and so on. I have a column where the payment months are specified in front of each person. I can also make 4 individual columns saying Q1,Q2,Q3,Q4 and specify the months under each if that makes it any easier for Excel to understand.



    I run monthly reports on 5th of each month to see who needs to be paid that month. I am trying to figure out a way to automate this process. So Excel will only pick the records that have System's (Computer's) Month mentioned in the Q1,Q2,Q3 or Q4 columns.



    Is there a function in excel that can do this or can someone help me with the code for this.



    Thanks in advance !