+ Reply to Thread
Results 1 to 9 of 9

I'm Stumped

  1. #1
    Registered User
    Join Date
    05-05-2006
    Location
    Kent, England
    Posts
    37

    I'm Stumped

    Can anyone tell me how I can use a drop down list of months (april - march) and use that list to show only certain rows on the worksheet - depending on what month is selected?

    It's a tricky one - help very much appreciated.

    ExcelBob

  2. #2
    Roger Govier
    Guest

    Re: I'm Stumped

    Hi

    Obviously your data includes a column with either Apr, May etc. or full
    Excel dates 01/04/2006.
    If the latter, then add a further column to the data which is just =A1
    (assuming your dates are in column A) but format the column
    Format>Cells>Number>Custom>mmm

    Then, you don't need a dropdown list of months, just mark your header
    row and Data>Filter>Autofilter
    Use the dropdown on the column with your date in month format and select
    the relevant month.

    --
    Regards

    Roger Govier


    "ExcelBob" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Can anyone tell me how I can use a drop down list of months (april -
    > march) and use that list to show only certain rows on the worksheet -
    > depending on what month is selected?
    >
    > It's a tricky one - help very much appreciated.
    >
    > ExcelBob
    >
    >
    > --
    > ExcelBob
    > ------------------------------------------------------------------------
    > ExcelBob's Profile:
    > http://www.excelforum.com/member.php...o&userid=34152
    > View this thread:
    > http://www.excelforum.com/showthread...hreadid=545411
    >




  3. #3
    Gary''s Student
    Guest

    RE: I'm Stumped

    Use Autoformat
    --
    Gary''s Student


    "ExcelBob" wrote:

    >
    > Can anyone tell me how I can use a drop down list of months (april -
    > march) and use that list to show only certain rows on the worksheet -
    > depending on what month is selected?
    >
    > It's a tricky one - help very much appreciated.
    >
    > ExcelBob
    >
    >
    > --
    > ExcelBob
    > ------------------------------------------------------------------------
    > ExcelBob's Profile: http://www.excelforum.com/member.php...o&userid=34152
    > View this thread: http://www.excelforum.com/showthread...hreadid=545411
    >
    >


  4. #4
    Forum Contributor
    Join Date
    05-05-2006
    Posts
    143
    Another Option would be to use a Pivot Table.

    and then you can see multiple selections grouped by month. and further more you can break each monthly grouping down by what ever other colums you have in your data set.

    So you could gather to gether results for March and April for instance. not just filter to just singular months.

    BUt AutoFiler is probably your best bet if you just want to review the data.


    Another thing you may want to look at to make things pretty is conditional formatting. Highlight the colulm with the months in and set commands to set the backgroup Blue/yellow etc what ever takes your fancy if the cell includes 'April'/'may' etc.

  5. #5
    Registered User
    Join Date
    05-05-2006
    Location
    Kent, England
    Posts
    37
    Thanks Roger

    What I've got is about 15 rows in column A with April, May etc written in. Then in B3 I have a drop down list of months and when you select April I want it to show only the 15 rows with April in column A.

    The reason I don't want to use Autofilter is because I don't want the user to be able to show 'all' rows at once, and when you use Autofilter the months run in alphabetical order as opposed to month order.

    Any ideas?

  6. #6
    Registered User
    Join Date
    05-05-2006
    Location
    Kent, England
    Posts
    37
    Thanks Sam

    But a pivot table is not really what I am after as once the month has been selected data needs to be modified on the sheet, this sheet then feeds into other worksheets depending on what month is selected.

  7. #7
    Roger Govier
    Guest

    Re: I'm Stumped

    Hi

    Then you could use Advanced Filter linked to a Worksheet Change event.
    Set up your cell B3 with the Data Validation list of Months in the order
    you want. Place the heading Month in cell B2 and in cell A1

    Right click on the sheet tab containing your data and copy the macro
    below into the white pane

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If Range("B3") = "" Then Range("B3") = "Apr"
    Range("A1:A1000").AdvancedFilter Action:=xlFilterInPlace,
    CriteriaRange:= _
    Range("B2:B3"), Unique:=False

    Application.EnableEvents = True
    End Sub

    Now, when you click on the dropdown on cell B3 and select the month,
    only the data for that Month will be visible.
    The above code forces cell B3 to have Apr selected, if the user tries to
    delete the value in the cell (thereby showing all data) as Data
    Validation will not prevent this.

    --
    Regards

    Roger Govier


    "ExcelBob" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Thanks Roger
    >
    > What I've got is about 15 rows in column A with April, May etc written
    > in. Then in B3 I have a drop down list of months and when you select
    > April I want it to show only the 15 rows with April in column A.
    >
    > The reason I don't want to use Autofilter is because I don't want the
    > user to be able to show 'all' rows at once, and when you use
    > Autofilter
    > the months run in alphabetical order as opposed to month order.
    >
    > Any ideas?
    >
    >
    > --
    > ExcelBob
    > ------------------------------------------------------------------------
    > ExcelBob's Profile:
    > http://www.excelforum.com/member.php...o&userid=34152
    > View this thread:
    > http://www.excelforum.com/showthread...hreadid=545411
    >




  8. #8
    Registered User
    Join Date
    05-05-2006
    Location
    Kent, England
    Posts
    37
    THANKS ROGER.

    Seems like I'm getting there but when I put that code in it is coming up with a syntax error and highlighting the line

    Range ("A1:A1000").AdvancedFilter Action:=xlFilterInPlace,

  9. #9
    Roger Govier
    Guest

    Re: I'm Stumped

    Hi

    It's the word wrap in the posting that's catching you out
    The complete line should be

    Range("A1:A1000").AdvancedFilter Action:=xlFilterInPlace, _
    CriteriaRange:= Range("B2:B3"), Unique:=False

    The VBE uses a space followed by an underscore before the line break if
    the line is too wide.
    Unfortunately with the word wrap in the email, it broke it in the wrong
    place.

    Try the above and it should work fine


    --
    Regards

    Roger Govier


    "ExcelBob" <[email protected]> wrote
    in message news:[email protected]...
    >
    > THANKS ROGER.
    >
    > Seems like I'm getting there but when I put that code in it is coming
    > up with a syntax error and highlighting the line
    >
    > Range ("A1:A1000").AdvancedFilter Action:=xlFilterInPlace,
    >
    >
    > --
    > ExcelBob
    > ------------------------------------------------------------------------
    > ExcelBob's Profile:
    > http://www.excelforum.com/member.php...o&userid=34152
    > View this thread:
    > http://www.excelforum.com/showthread...hreadid=545411
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1