Filter table without using Auto Filter

[sudipto3003]

Dear all,

I have a data table where user should fill up the monthly value, except the data entry portion management wants other fields/cells will be locked even auto filter also. But the users require at least one field to be filter and that is the "Div.". My question is how can i do that, i have also add a special cell "E1" where all the divisions are in a drop down menu, is there any way that one should select a division from drop down list and the entire data table should be shorted? i have also attach the file herewith and looking for your sugesstion. Thanks in advance.

 

[lohithsriram]

Hi Sudipta,

You can do this by locking the portion of your cells and protecting the sheet. I cannot upload the file and hence the steps to do it.

1) Apply Autofilter only to Column E (Div)
2) Select all the cells ( Ctrl + A), Right Click -> Format Cells -> Go to Protection tab and check option "Locked" -> OK
3) Select Columns E to R , Right Click -> Format Cells -> Go to Protection tab and and Uncheck option "Locked"-> OK
4) Go to Review tab, click on Protect sheet. -> on the options provided, keep only "Select Unlocked Cells" and "Use AutoFilter" option ticked -> Enter password -> Click OK -> Reenter Password -> OK

You don't need the E1 Cell with the above. Try and let us know, if this helps.

 

[sudipto3003]

thanks lohithsriram, i have already presented this method in front of my management, but thery are stick in their point 'no auto filter', i don't know why? if no any other solution is there then i have to do that.

 

[Marc L]

Hi,

is any brain ressources in your management ?‼ How they justify that ?

_________________________________________________________
Management is doing things right, leadership is doing the right things. (Peter Drucker)
​

 

[lohithsriram]

Hi Sudipta,

Wondering why they don't want you to use the Autofilter!!! Anyways, you can use VBA code to do the same task as the Autofilter does. But this is not as swift as the Autofilter does.

Add the below code to your Sheet module. (Alt +F11 to open the VBA editor and paste it in sheet1(Cost Centre Wise heads) sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Rows().EntireRow.Hidden = False
Application.ScreenUpdating = False
If Target.Address = "$E$1" Then
      For i = 5 To ActiveSheet.Cells.SpecialCells(xlLastCell).Row
        If Not Range("E" & Trim(i)).Value = Target.Value Then
            Rows(i).EntireRow.Hidden = True
        End If
      Next
      Debug.Print i
End If
Application.ScreenUpdating = True
End Sub

 

[sudipto3003]

dear lohithsriram,
thanks for your second sugesstion, and it realy works fine. one more question i have here as i am not very friendly with vba, if i mail the file now to the users will the macro function be same?

i must say to the members that chandoo.org is realy helpful for us, don't pass any needless comments if you don't have any suggesstion.

 

[lohithsriram]

Hi Sudipto,

If you are with Office 2003, do not worry. You can send it to any one on the email. If you are with Office 2007/2010 or higher, please make sure you save the file as .xlsm format (Excel Macro- Enabled Workbook) . You dont need to change anything on VBA. Wherever the file goes, it works the same.

 

[sudipto3003]

thank you lohith

 

[json]

I have another idea.... a little late to the party, i know... and it still involves a macro (but much simpler code).

in short, we could use the advanced filter tool and a macro to refresh the filter when a new Div. is selected from the drop down:

heres how:
insert a new row1. this will put youer drop down in E2. copy the Div. head from your table (e4) and paste it in the new E1 cell. now, select any cell in your data and go to Data-Sort&Filter Group-Advanced. Leave the first option as filter in place (you could also choose to show the results on a different sheet if you'd like but this will require starting this process differently), set your 'List range' (mine shows: $A$4:$T$3893). now we want to set the criteria range to E1:E2. then hit ok.

now at first it will filter your data to show only those records that belong to whichever Div was already selected. if you go select a new Div you'll notice that the filter doesnt automattically refresh itself. all you'd have to do here is 'record a new marco' that ONLY consists of you opening the advanced filter window and hitting ok (the ranges will remain the exact same, but this will 'refresh' the data.) so now all you'd have to do is select a Div from your list, and run this macro!! IF IT WERE ME, i'd insert a shape (like a button) with the word "Filter" on it and set that shape to run the macro you recorded and put it right next to your drop down. that way, when somoene selects a new Div. they'd just hit this button to refresh the results!!

one limitation that i can think of with this (without doing any work around) is that it will only show the records per the filter. but all we'd have to do there is create a macro that clears the fitler.

also, i've noticed that if i select footwear "footwear new" is also included in the resutls....

 

[AIM]

Hi json,

A workaround to the limitation that you mentioned ("i've noticed that if i select footwear "footwear new" is also included in the resutls") would be to enter your criteria as

="=footwear"

in another cell and use that as your criteria range in the adv filter.

Here is an example of how it can be used in this scenario.