Results 1 to 7 of 7
  1. #1
    Azri1
    Guest

    make excel runs like ms access query...

    hello again ,

    what i like to do is make a query like ms access can do.
    on sheet1 is the database that where all the particular
    are stored. on sheet2 is like a search engine. what i
    like to do is when i put data in cell A1 in sheet2,
    i will push the button search and it will show me
    the data that i required..

    e.g let say i put 'DOCTOR' in cell A1 in sheet2, and
    after push the button search, all the the name that
    works as doctor will appear at with their particular
    details in the cell that i've provided in sheet2 starting from
    B6 i.e :

    MELISA FEMALE 25 DOCTOR
    RAUL MALE 25 DOCTOR
    HUNTER MALE 25 DOCTOR

    i can put what ever keyword that i like and the result will appear.
    1 more example is let say if i put 'ENGINEER', so the result will
    show only 1 personal detail that is 'JOHN' with his personal details..

    is it possible..many thanks..hope you genius here can make this things
    become reality..hope to hear positive reply from all of you excel genius
    here..thanks


    ps: plz refer to this workbook and the sheet call 'DEMO'

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

    Re: make excel runs like ms access query...

    Azri,

    It may not be Access, but the enclosed simply uses the the Advanced Data Filter capability of Excel. This method allows you to search on more than one field at a time. I am attaching your w/b with and additional sheet called Suggestion. I named the data list in Sheet1 as DATABASE, and gave it a dynamic refers to range so that if you add more data it will be included. See if it meets your needs.

    Andrew
    Attached Files Attached Files

  3. #3
    Azri1
    Guest

    Re: make excel runs like ms access query...

    thanks andrew..i will study it right now and will post
    a feedback..regards

  4. #4
    Azri1
    Guest

    another question related to this..

    let say i only want to find the data in the age column.
    how do i manipulate your method Andrew and using wildcard like 2*. it works with text.how can i combine it with number..plz refer attachment

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

    Re: another question related to this..

    Azri,

    Wildcards with numbers is not a very good idea, e.g. 2* would give any value starting with 2, such as 2, 23, 235, 2333 etc, when I presume you want Ages between 20 and 30. Even for that to happen you have to convert the numbers to text. It is possible to have more than one condition for a field, so I have set 2 AGE fields in the criteria range. In the first one you can enter >=20 and in the second <30, which will give you all ages from 20 to 29. I have also included an additional row so that you specify OR conditions. For this to work properly I have put an indicator value in G3, which will tell the search macro if the OR row is in use. So before setting new criteria you should use the clear button provide to avoid any unwanted spaces etc.

    I have included some examples for you try.

    Hope it works the way you would like

    Andrew
    Attached Files Attached Files

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

    Re: another question related to this..

    Azri,

    When enetering the code I must have hit > instead of . on the first line. Please amend as follows :

    If Range("G3").Value <> 0 Then

    in place of

    If Range("G3") > Value <> 0 Then

    Sorry, and hope I did not inconvenience you

    Andrew

  7. #7
    Azri1
    Guest

    Re: another question related to this..

    thanks andrew..it solved my problem..hehe..lol..

    you such a doll..many thanks.regards and cheers

Posting Permissions

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