Advanced Filtering and Data Entry
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 DataFilterAdvanced 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 a 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" wrote:
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
|