On Jun 23, 3:00*am, cush <c...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "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- Hide quoted text -
>
> - Show quoted text -
I followed your instructions, however, the macro did not record
anything. Am I doing something wrong?
|
Similar Threads
