Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extracting Information (2003)

    Change Extracted!D5 to Location, so that D56 becomes a valid criteria range.
    Select D56 on the Extracted worksheet.
    Select Data | Filter | Advanced Filter...
    Set the List Range to Details!C5:H15
    Set the Criteria Range to D56
    Tick Copy To Other Location.
    Set the Copy To box to F7:K7
    Click OK

    (Thanks to <!profile=Aladin Akyurek>Aladin Akyurek<!/profile> for pointing out that you must start on the destination worksheet.)

  2. #2
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Extracting Information (2003)

    Hans, Thanks for the quick reply - that works fine (just me doing it wrong before). I was hoping that there maybe a way to be able to extract details based on the site list using some formula maybe an array or something, so that there was no need to adjust the advanced filter each time - is that possible?

    Dean

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extracting Information (2003)

    >> using some formula

    I don't know how to do that, perhaps one of the Excel gurus has a suggestion.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting Information (2003)

    You could create macro to do the adv filter with all the info defined (you could even expand /contract the the datatable at runtime). You could then create workbook change event so that when you change the "criteria" cell that the you run the macro so all you would have to do is change the value.
    [Note: you can not use this with datavalidation since that does not trigger a change event. You would have to manually enter the number.]

    If you needed a pulldown, you could crete one using the forms toolbar combobox and assign the macro to that object and place the linked cell as the criteria range so it would function "like" the datavalidation. You could even palce the combobox over cell D6.

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Extracting Information (2003)

    Thanks Guys, This is obviously not a simple task - I think the best option is to stick to the advanced filter option.

    Thanks for your suggestions

    Regards

    Dean

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Information (2003)

    Verada,

    You might want to look at an example of extracting data that I put together in <post#=353069>post 353069</post#>

    Paul

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extracting Information (2003)

    The easiest way is to use advanced filtering with VBA, as Steve suggersted. You can use the Worksheet_Change event to intercept any change to the selected criteria, and proceed to do the filter with code.The following example code should get you started. Note I have used Range Names rather than actual celll addresses as it allows you to alter your layout without having to alter the code.<pre> Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target.Cells(1), [Criteria].Cells(2)) Is Nothing Then
    [DataList].CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=[Criteria], _
    CopyToRange:=[Extract]
    End If
    End Sub</pre>

    Attached find your workbook with the above implemented.

    Andrew C

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Extracting Information (2003)

    Paul.

    That looks like something I can use. Is it just a matter of defining the names and using the two macros?

    Thanks

    Dean

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting Information (2003)

    As I mentioned (and you can see in your workbook) it only works if you manually type in the value. If you use the the datavalidation list, the change event is not triggered (defeatinig the purpose of the validation list)

    Here are a couple workarounds
    I added your code to a modue:
    <pre>Sub ExtractData()
    [DataList].CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=[Criteria], _
    CopyToRange:=[Extract]
    End Sub</pre>


    Then I added (as examples) a spinner button (adduming the real values desired were numbers 1-9) that I made the cell link to D6 and assigned the above macro. So when the spinnerup or down is pressed, it changes the number and runs the macro.

    Another option (with numbers or a list) is a combobox. I used as the range, the same range used in validation and assigned the link to D6 and assigned the macro above to it. Now when a selection is made in the combobox, the data is extracted.

    I would use one or both of these solutions and remove the datavalidation. The combobox may be placed over the cell. This will essentially work like validation. If numbers I like the spinner, since it is less clicking to "browse" thru the list.

    Steve

  10. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Extracting Information (2003)

    Andrew,

    That is great!! - Thanks very much for your efforts.

    Regards

    Dean

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extracting Information (2003)

    <hr>As I mentioned (and you can see in your workbook) it only works if you manually type in the value.<hr>
    Not so with my system (XL2002). Changing the criteria by using the DataValidation dropdown does trigger the event. I did try that before posting the spreadsheet. As it works with 2002, I assume it also works in 2003, and I do think I have done something similar in XL2000.

    Andrew

  12. #12
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Extracting Information (2003)

    Hi All, I'm trying to figure out how to extract data from one worksheet to another - the attached sample shows what i need to do. I tied vlookup but only got the first record that match the criteria, also tried advanced filters but appears that you can only do this in the active worksheet - maybe that I'm doing both wrong!?

    Dean <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extracting Information (2003)

    It must be an XL97 issue. I have never found an event that is triggered in XL97 with datavalidation. For me, it hasn't been an issue, since I tend to use datavalidation only to "pop up" information and not for validation. I prefer the items in the Forms toolbar or the Control Toolbox.

    Steve

  14. #14
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Extracting Information (2003)

    Thanks Steve - Thats great!!

    Regards

    Dean

  15. #15
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Information (2003)

    Dean,

    You asked about my other post of an example. To make it more familiar to you, I have taken the above attached file by Steve and expanded it a little to show how you can Search in fields other than just the Location field. Simply type your search criteria in the green cells to get the results.

    Note you can use symbols such as > (greater than ) with a date to get all records after that date or <C to get only names which begin with A or B. Also complete words are not necessary: "y" will give you all "yes" records in the Required field.

    Also you may use combinations, such as: >2/5 in the Date field and " y " in the Required field

    If you prefer searching for exact matches, use a drop-down box like Steve's --- which I moved to cover the green cell below Location and then linked to that cell.

    Basically, what we have offered here are several adaptations on the same theme: Extracting certain records from a database.
    HTH

    Paul

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •