Advanced Filter

Hi,

I am appealing to someone’s better nature to help me adapting a spreadsheet I found on the contextures website (http://www.contextures.com/excelfiles.html#Filter). I love the concept of this and I am trying to create something similar but my skills severely lack.

Even if you can help me on just one of these problems below I would greatly appreciate it.

Problem 1:
Please note that this file contains words associated with movies – If I change the names the model doesn’t work which is the first problem I am stuck with. I need to change the names to the column headings in the perfect sheet

Problem 2:
Currently the model only works with two drop down lists – how can I change this to 5 (as in the perfect tab)

Problem 3:
I have no idea how to limit the model to include either one or a list of customers, can this be done?


Sheet Overviews
Perfect Sheet
This is ultimately what I am trying to achieve. Here the user can select a customer and a range of fields and the table with update depending on the user selection

Overview
This is basically a dump of the information I receive which I am trying to present in a better format

Movie List
This will become my overview tab in time

Select Movies
This will be the user form in time (I need it to look like the perfect tab)



Can anyone help in any way – even if you point me in the right direct I will endeavour to solve this. I understand I have a lot of work to do but any help anyone can offer I will be grateful for

Paul

Hi Paul,

I wrote some code a while back for a electronics parts guy. I called the file Dell Parts Adv Filter and it (the ideas) might work for your problem.

It looks like you want to do an advanced filter of your data based on some input. I did this in the attached by double clicking on a sorted list of 3 criteria. If you double click under the Yellow lists it will filter the the results.

If this type of method works for you, we could try to modify it to you workbook.

Very easy.

I've used Advanced Filters with a Criterai Range that can hold dropdown values/text. You need to read a little about Advanced Filters and the Criteria so you get your dropdown on the same row under the column headdings. See:
http://www.contextures.com/xladvfilter01.html or
http://www.excelfunctions.net/ExcelAdvancedFilter.html
http://www.ozgrid.com/News/excel-advanced-filter.htm

Hi Paul,

Try this. On the Criteria sheet I've done Data Validation and a Macro to do Advanced Filter.
NOTE - the criteria headings MUST match the data headings (spelling exactly)
I hope this is what you want.

Can anyone advise me how the entries are made on the "criteriasel" sheet please?

This is the code if anyone has time to look at it?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCrit As Range

Set rngCrit = wksCrit.Range("CriteriaRng")
Application.EnableEvents = False

Select Case Target.Address
    Case Range("SelPro").Address
        rngCrit.Cells(2, 1).Value = Target.Value
    Case Range("SelGeo").Address
        rngCrit.Cells(2, 2).Value = Target.Value
    Case Range("SelSpacer").Address
        rngCrit.Cells(2, 3).Value = Target.Value
    Case Range("SelText").Address
        rngCrit.Cells(2, 4).Value = Target.Value
    Case Range("SelGlass").Address
        rngCrit.Cells(2, 5).Value = Target.Value
End Select

If Range("SelPro").Value = "" Then
    rngCrit.Cells(2, 1).ClearContents
End If
If Range("SelGeo").Value = "" Then
    rngCrit.Cells(2, 2).ClearContents
End If
If Range("SelSpacer").Value = "" Then
    rngCrit.Cells(2, 3).ClearContents
End If
If Range("SelText").Value = "" Then
    rngCrit.Cells(2, 3).ClearContents
End If
If Range("SelGlass").Value = "" Then
    rngCrit.Cells(2, 4).ClearContents
End If

If Not rngCrit Is Nothing Then
  wksMovies.Range("GlassList").AdvancedFilter _
    Action:=xlFilterCopy, _
      CriteriaRange:=rngCrit, _
      CopyToRange:=Range("ExtractData"), Unique:=False
End If

exitHandler:
    Application.EnableEvents = True
    Exit Sub
errHandler:
    Resume exitHandler

End Sub

Hi,
I'm looking at the first line of your code that starts with PRIVATE meaning the code is behind a worksheet instead in a module. This implies that the code will only work on data on that individual sheet. It will only interact with procedures in that worksheet's module.

You have a lot of named ranges in your code. Is it possible that one of the named ranges is on another sheet? If so I don't believe a Private routine can deal with ranges outside it's worksheet.

The correction is to put your code into a module, make it public and jump to it from the event procedure.
See http://www.cpearson.com/Excel/Scope.aspx or
http://vbatutor.blogspot.com/2009/02...procedure.html