I want the list Sheet1!A6:E35 to be auto filtered if I choose a value from the validation list at A1. How can I do that?
Please help.
I want the list Sheet1!A6:E35 to be auto filtered if I choose a value from the validation list at A1. How can I do that?
Please help.
Hi heidiau,
I took your dummy workbook and recorded a macro to do an advanced filter in place. I needed to add a row 1 to have the word "Teacher" above the cell that changes. Then I put an event macro behind sheet one that would fire when A2 changed. It then calls the Macro2 to do the Advanced Filter.
Hope this example helps you figure it out.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi MarvinP,
thank you for your suggestion, but I have no idea about macro. I tried to copy the code to my file, but it doesn't work. Are there any other ways to obtain the same result without using macro?
OK -
Excel has a feature built into it for this exact type of problem. It is called Auto Filters. See the attached where you don't need a validation dropdown. You simply do an auto filter with your data. See the attached and read:
http://www.contextures.com/xlautofilter01.html
thank you for the information. but this cannot fit my needs.
I'll figure out the macro things.
thank you.
i can't go to the link you gave me, but i tried to record macro and i failed. below is my steps:
design mode
record macro
data>advanced filter (in-place)
stop macro
what did i miss?
Hi
Try thisThis is array formula? Press same time "Ctrl+Shift+Enter" NOT ENTER till you see both end like this {}, then copy down and cross.Formula:Please Login or Register to view this content.
or
AGGREGATE? This only work excel 2010 onward? Won't work before 2010, if you on excel 2003 or 2007
Enter, then copy down and cross.Formula:Please Login or Register to view this content.
See the file.
To help you by my post? it would be nice to click on to say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
hi micope21,
when i want to show all again and clear content at Sheet1!A1, the data in the table changed to "0"
hi MarvinP,
I figure out how to record advanced filter. it works a the first attempt. but after i closed the file and opened the file again, the macro and the event on change code disappeared. why's that?
Hi,
I think you saved the file as an ".xlsx" instead of a ".xlsm" file type. The "m" stands for "macro". Try to save the file as a macro enabled file using the .xlsm extension on the filename.
hi MarvinP,
i can apply the macro codes to my excel. thank you very much. it helps me save a lot of time when preparing documents.
Change to this if there a blank cell A1
Copy down. This will remove 0 to blank.Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks