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

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" <ExcelBob.28cuoz_1148546701.5304@excelforum-nospam.com> wrote
in message news:ExcelBob.28cuoz_1148546701.5304@excelforum-nospam.com...
>
> 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
>

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
>
>

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.

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?

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.

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" <ExcelBob.28cwry_1148549401.4049@excelforum-nospam.com> wrote
in message news:ExcelBob.28cwry_1148549401.4049@excelforum-nospam.com...
>
> 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
>

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,

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" <ExcelBob.28d30y_1148557501.6181@excelforum-nospam.com> wrote
in message news:ExcelBob.28d30y_1148557501.6181@excelforum-nospam.com...
>
> 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
>