Advanced Filtering and Data Entry

[Scott Halper]

I have a massive data set of contact (including name, company, city,
state, email, notes, comments, etc.) I am trying to figure out how to
have another sheet where I can select certain values (i.e. - a city or
company name) and then have all the data that matches that criteria
display below. In addition, if i enter in or change any of the data,
I want to the data set (the other sheet) to record the changes or
updates (also, if i type in a new value entiring, it would need to be
recorded as well). I'm not sure if this is even possible, but any
help if greatly appreciated.

Thanks in advance for the help.

Scott

 

[cush]

Scott,

you can use Advanced Filtering for this. I usually set up my database
something like this --- Adjust as needed

In sheet1
Headers in C5 to L5
Data in C6 to L1000

Dynamic Named Range: Db=Offset($C$5,0,0,CountA($C$5:$C$65536),10)

Then copy Headers to C2:L2
and Name Range C2:L3 as CriteriaRng
Name the Range C3:L3 as CriteriaCells
I also color this rng and type the word "FIND: " in cell B3

Then copy Headers to Sheet2.Range(C5:L5)
and Name this Range --- ExtractRng

Now, go to sheet1 and turn on the Macro Recording, Name the macro something
like:
SearchDb

Click on Data>Filter>Advanced Filter
Select Copy to another location

List Range: Db
Criteria: CriteriaRng
Copy to: ExtractRng

Click OK. Turn off recording

RtClick the Sheet1 Tab> View Code and enter the following code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Range("CriteriaCells"), Target) Is Nothing Then
SearchDb 'the macro you just recorded
End If
End Sub

Now, you are ready to run the code.
Suppose your Header H5 and Criteria H2 are: City
and suppose you have in the Db records for Seattle, San Francisco, St. Louis
and Bellevue

Entering the letter S in cell H3 (plus the Enter key) will extract all
records for cities starting with "S" from your Db and paste them below the
ExtractRng on Sheet2

You can even use operators like <S which will return cities coming before S,
alphabetically, (Only Bellevue in the case above).

Of course, the more letters or numbers you enter in a CriteriaCell, the more
restricted your search will be.

Clearing all of the CriteriaCells, will extract every record.

You can also Filter In Place instead of copying to Sheet2

Last of all when a user enters more records, to avoid a lot of overhead
event code, I would create a command button and assign the macro SearchDb to
it on Sheet1.
If you try to do it automatically with another Worksheet_Change event, you
could end up triggering the code for each field entered in a record.

Hope this helps

 

[Scott Halper]

Scott,

you can use Advanced Filtering for this.  I usually set up my database
something like this --- Adjust as needed

In sheet1
Headers in C5 to L5
Data in C6 to L1000

Dynamic Named Range:   Db=Offset($C$5,0,0,CountA($C$5:$C$65536),10)

Then copy Headers to C2:L2
and Name Range C2:L3  as CriteriaRng
Name the Range C3:L3 as CriteriaCells
I also color this rng and type the word "FIND:  " in cell B3

Then copy Headers to Sheet2.Range(C5:L5)
and Name this Range --- ExtractRng

Now, go to sheet1 and turn on the Macro Recording, Name the macro something
like:
SearchDb

Click on Data>Filter>Advanced Filter
Select Copy to another location

List Range:    Db
Criteria:        CriteriaRng
Copy to:        ExtractRng

Click OK.  Turn off recording

RtClick the Sheet1 Tab> View Code and enter the following code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Range("CriteriaCells"), Target) Is Nothing Then
        SearchDb    'the macro you just recorded
    End If
End Sub

Now, you are ready to run the code.
Suppose your Header  H5 and Criteria H2 are:   City
and suppose you have in the Db records for Seattle, San Francisco, St. Louis
and Bellevue

Entering the letter S in cell H3 (plus the Enter key) will extract all
records for cities starting with "S" from your Db and paste them below the
ExtractRng on Sheet2

You can even use operators like <S which will return cities coming beforeS,
alphabetically, (Only Bellevue in the case above).

Of course, the more letters or numbers you enter in a CriteriaCell, the more
restricted your search will be.

Clearing all of the CriteriaCells, will extract every record.

You can also Filter In Place instead of copying to Sheet2

Last of all when a user enters more records, to avoid a lot of overhead
event code, I would create a command button and assign the macro SearchDbto
it on Sheet1.
If you try to do it automatically with another Worksheet_Change event, you
could end up triggering the code for each field entered in a record.

Hope this helps







- Show quoted text -

I followed your instructions, however, the macro did not record
anything. Am I doing something wrong?