none
[EXCEL] dropdownlist month & year autofill weekdays RRS feed

  • Question

  • Hi all,

    I'm fairly new to VBA and would appreciate any help you can provide with the following problem. I am trying to make a spreadsheet that records the number of hours I've worked each month.

     

    I have two drop down lists: one containing months (in the format "January", "February"...) the other containing years ("2011", "2012"...).

    When I select any combination I want Excel to autofill a column with the weekdays of that month in the format dd/mm/yyyy.

     

    I'm not sure where to begin, but I have recorded a macro by manually filling in the first date of the month and using the autofill feature down the column; I just don't know where to go from here...

     

    Cheers,

    Jim


    Monday, August 22, 2011 12:30 PM

Answers

  • Hi Jim,

    I just thought I'd say thanks for your help. Although I couldn't get your method to work, it put me on the right track.

    I ended up with a more complex solution that required a few formulas spread over a couple of columns; but it does what I need it to do.

    I would post my solution but it would take me too long to explain! and there's no file attach feature on this forum (that I can find).

    Cheers,
    Jim

    Tuesday, September 6, 2011 10:50 AM

All replies


  • You don't need VBA to do it.
    If your linked cells are in J1 and M1, with the months & years dropdown lists directly below those cells...
    Enter this formula in H2:   =EOMONTH(INDIRECT("J"&J1+1) & " " & INDIRECT("M"&M1+1),-1)
    Enter this formula in G2 and fill down (about 24 rows):  =WORKDAY($H$2,ROW()-1)
    Format cells to your taste - you are done.
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (Data Rows add-in: Color rows, Delete rows, Insert rows)


    Monday, August 22, 2011 10:07 PM
  • Hi Jim,

     

    Many thanks for your reply. I have tried implementing your solution but I don't seem to understand it even after using the help. I'm getting an "Error in value" on the cell with the formula you specified beginning =EOMONTH...

    I shall clarify my sheet layout and I hope you or someone else can spend a minute or two helping me to fix it!

     

    Cell B5 - Data validation DDL which is picking up the months from D8:D19 (Col D is hidden / 0 width)

    Cell C5 - Data validation DDL which is picking up the years from E8:E19 (Col E is hidden / 0 width)

    Cells BC8:BC35 -  is where I'd like the autofilling workday list of dates, automatically up dating when either the month or year is changed in the DropDownList (DDL).

     

    All are formatted as 'Text' - this is where I think the problem lies; I don't understand what format the months list and years list need to be in for the formula =EOMONTH... to be in. I have tried using a custom format of 'mmm' for the months and 'numerical' for the years but this isn't working either.

    Any and all help is appreciated.
    Cheers,
    Jim

    Tuesday, August 23, 2011 8:31 AM
  • The End of Month function uses (needs) the Excel Analysis ToolPak. 
    The Atp is an Excel add-in and must be manually installed in XL versions released prior to xl2007.

    Use a "General" (the default) cell format to start and then change it if you need something different displayed in the cell.

    The EOM formula is used to determine the last day of the prior month.  That last day is used in the "Workday" function.
    '---
    Jim Cone
    Portland, Oregon USA
    http://excelusergroup.org/media/
    (Formats & Styles xl add-in:  lists/removes unused styles & number formats) - free

    Tuesday, August 23, 2011 12:28 PM
  • Hi Jim,

    I just thought I'd say thanks for your help. Although I couldn't get your method to work, it put me on the right track.

    I ended up with a more complex solution that required a few formulas spread over a couple of columns; but it does what I need it to do.

    I would post my solution but it would take me too long to explain! and there's no file attach feature on this forum (that I can find).

    Cheers,
    Jim

    Tuesday, September 6, 2011 10:50 AM