Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Still stumped | Excel Worksheet Functions | |||
Stumped by a lookup scenario | Excel Worksheet Functions | |||
Simple But Stumped | Excel Discussion (Misc queries) | |||
List box has me stumped. | Excel Discussion (Misc queries) | |||
Stumped! Collecting values into one cell. | Excel Worksheet Functions |