Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Auto set days of the week (Excel 2003)

    Hi all....does anyone have a 'date' or 'day' formula that will automatically insert days of the week in a column? I am attaching a sample.

    I want to be able to use a drop-down menu to insert a day of the week (eg: Wednesday) in cell B2 and have the succeeding days (Thurs, Fri, Sat, Sun, Mon, Tues) populate cells C2~H2 in sequence.....thanks
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto set days of the week (Excel 2003)

    You could use code in the Worksheet_Change event:
    - Right-click the sheet tab.
    - Select View Code from the popup menu.
    - Enter or copy/paste the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
    Application.EnableEvents = False
    Range("B2").AutoFill Range("B2:H2"), xlFillDays
    Application.EnableEvents = True
    End If
    End Sub

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Auto set days of the week (Excel 2003)

    And here's a formula approach.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Auto set days of the week (Excel 2003)

    Thank you for that Rory, altho your formula fills in the days in the column below....I need the days to populate cells to the right of the first one, so that if, for example I enter Tuesday in cell B3, then Wed, Thurs, Fri, Sat, Sun, Mon will populate the adjacent cells (C3,D3,E3,F3,G3,H3) in the same row.....can you adjust your formula to permit this?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Auto set days of the week (Excel 2003)

    Oops - sorry about that; didn't read the question closely enough. See attached revised version.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •