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


--
ExcelBob
------------------------------------------------------------------------
ExcelBob's Profile: http://www.excelforum.com/member.php...o&userid=34152
View this thread: http://www.excelforum.com/showthread...hreadid=545411

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
FormatCellsNumberCustommmm

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

--
Regards

Roger Govier


"ExcelBob" wrote
in message ...

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

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

I'm Stumped
 

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.


--
samprince
------------------------------------------------------------------------
samprince's Profile: http://www.excelforum.com/member.php...o&userid=34168
View this thread: http://www.excelforum.com/showthread...hreadid=545411

I'm Stumped
 

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

I'm Stumped
 

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.


--
ExcelBob
------------------------------------------------------------------------
ExcelBob's Profile: http://www.excelforum.com/member.php...o&userid=34152
View this thread: http://www.excelforum.com/showthread...hreadid=545411

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" wrote
in message ...

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

I'm Stumped
 

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

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" wrote
in message ...

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