Creating a searchable database

**Khanzaki** · 07-20-2013, 06:37 AM

Hi Team,
I have 2 queries hoping to get it solved as always!
1. I want to use excel as a searchable database .I have googled it but all their suggestions are to store data online (web forms ) which I don’t find useful. I searched this forum and here is the link which is nearly same(It only searches by name while I want the search to be based on two value ie name and paid or not).
If you could design the code according to my attached Demo.xlsb would be cool as I would be able to figure out whether it suits my requirement or not. (The actual file contains thousand of data but the format is same)
2. Second Question Is in the workbook itself.
Waiting for your replies.
Thanks in advance

**oeldere** · 07-20-2013, 06:46 AM

Why not just filter on the criteria?

**Khanzaki** · 07-20-2013, 07:02 AM

Hi thanks oeldere for your quick response !

Well would you do that for me and upload here please so that I can figure out whether will that do or not... As I am not familiar to filter ..
Also have a look at my second question that's most important
Thanks

**oeldere** · 07-20-2013, 07:05 AM

With an filter.

**Khanzaki** · 07-20-2013, 07:16 AM

Ummm not exactly as I have huge list of around thousands of customers.and every time it would be tedious to go through all the name to get one single name is not logical ... beside the search criteria box remains unused ... I want to make use of it so that it becomes more user friendly..

**oeldere** · 07-20-2013, 07:31 AM

With pivot table.

See the attached file.

**Khanzaki** · 07-21-2013, 01:31 AM

Hi oeldere ...
I feel I have made my issue a little complex ..my requirements can be achieved by pivot tables and even filter .
I googled a lot and found the attached file (Invoice aging report.xltx) I want my workbook to be just like that except the paid column (column G of invoices) ..
But still I have no replies of my second question need that solution badly.

**oeldere** · 07-21-2013, 03:59 AM

For your second question, see the yellow cells in the attached file.

**Khanzaki** · 07-21-2013, 02:40 PM

Wow that does what I want but still if I drag this formula for 1000 rows will that not slow up my workbook ??
I feel it will and that's why I am searching for a macro that would do the same any idea how to achieve it!!

**oeldere** · 07-21-2013, 02:45 PM

for that kind of rows (1000 row) that can't be a problem.

but you will know if you test it on a dummy file.

**Khanzaki** · 07-21-2013, 02:50 PM

Okay it seems I have got my solution ... thanks oeldere for your kind effort to help me out .. I have few more questions that I would ask in my next post
In Sha'a Allah will wait for your response

**oeldere** · 07-21-2013, 02:55 PM

In Sha'a Allah will wait for your response

What will this mean; I don't understand.

You better post the questions related on this topic in the same topic, instead of making a new treat.

**Khanzaki** · 07-22-2013, 03:00 AM

I meant to say "I have few more questions that I would ask in my next post In Sha'a Allah(if Allah wills), will wait for your response"
By the way I worked out on my original workbook but yea one thing is missing I wanted the sorted data to copied to a new worksheet sadly your formula doesn't do so... Is there any macro or vba solution you must be wondering why this guy is stressing on macros its simply I love it.

However if you could tell me how the sorted data be copied to a new worksheet would be great
As I have dragged down the formula for 1000 rows what it does it calculates dues count even if there is no value in date column is there any way to avoid it ??
Thanks

**Khanzaki** · 07-27-2013, 01:13 AM

Why am I not getting any replies Oeldere where have you gone ?? need your all attention here

**oeldere** · 07-27-2013, 03:46 AM

Post your file, wihtout confidentional information.

Please also add the desired, expected result in the file.

**Khanzaki** · 07-27-2013, 08:00 AM

Did as you said

**oeldere** · 07-27-2013, 08:08 AM

Plaese read #15 again.

What should be done?

**Khanzaki** · 07-27-2013, 08:10 AM

I have mentioned that in the workbook itself for better understanding of situation have a look

**oeldere** · 07-27-2013, 08:14 AM

Why am I not getting any replies Oeldere where have you gone ?? need your all attention here

Post your file, wihtout confidentional information.

Please also add the desired, expected result in the file.

Did as you said

Plaese read #15 again.

What should be done?

I have mentioned that in the workbook itself for better understanding of situation have a look

**Khanzaki** · 07-27-2013, 08:33 AM

what I want this worksheet to do is when I enter company name in column D1 of sheet1 it should return me value of only that particular company in Result worksheet and can the cell D1 be made a suggestion giving like when I enter Ad it should suggest me adi hardware

**oeldere** · 07-27-2013, 08:43 AM

Since you don't put the desired result in the file (I asked you several times),

and you don't feel the need to do that for me,

I will quit with this question.

**Khanzaki** · 07-27-2013, 08:47 AM

Hi oeldere ,
I have always mentioned my desired result. Have a look in cell L3 and also in #21 please correct me if I am wrong

**Khanzaki** · 07-27-2013, 02:01 PM

Okay I will try my best to be upto the mark this time .
What I want from the attached worksheet is when i start typing in cell D1 of sheet 1 it should suggest me the matching companies name then I would choose one of it and soon as I press enter all the data of the selected company be copied to worksheet Result

**Khanzaki** · 07-30-2013, 04:55 AM

I have tried once again to explain please have a look on the attached file .i would be glad if anybody comes out with a reply and oeldere i need your responses too .
What this worksheet does is it collects data from different workbooks in Sheet DATA fine, ! now when i want to evaluate data i go to sheet RESULT and enter companies name in cell B1 and hit Search button it should return all the data related to that company.
For example if I enter Adi Hardware it should collect all data from Data worksheet related to Adi Hardware and copy it in sheet Result as I have tried to show in the workbook ... Have a look this time its simpler and self explanatory (only after you confirm )
Thanks i am in dire need of it

**Khanzaki** · 07-30-2013, 08:52 AM

Bump no response

**apo** · 07-30-2013, 09:49 AM

Why am I not getting any replies Oeldere where have you gone ?? need your all attention here

I think your being a bit presumptuous that anyone here owes you their time.. the word ingrate is coming to mind....

If you want help in a manner that is more suited to your impatience, then perhaps go to the "Commercial Services" section of the forum..

**mikerickson** · 07-30-2013, 09:53 AM

A slight rearrangement of your input area on Result! works with this code

Sub FilterChosen()
    Dim DataRange As Range
    Dim critRange As Range
    Dim DestinationRange As Range
    Set critRange = Sheet3.Range("A1:A2")
    Set DestinationRange = Sheet3.Range("A6")
    With Sheet2
        With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            Set DataRange = .Resize(.Rows.Count, 7)
        End With
    End With
    DestinationRange.Resize(10000, 7).Clear
    DataRange.AdvancedFilter action:=xlFilterCopy, criteriarange:=critRange, copytorange:=DestinationRange, unique:=False
End Sub

**Khanzaki** · 07-30-2013, 11:26 AM

Hi mikerickson you are genious it does exactly as I wanted now I will test it on my original template and will give the feedback ...
@ apo I know I have been a bit crazy last two days but try to understand my situation too .. Last 10 days and still people are unable to understand my question that's pretty obvious for a person to make him go impatient ... and Ingrate ?? probably not but ya if you are happy with that assumption I say go ahead

**Khanzaki** · 07-30-2013, 02:08 PM

Hi mike !!
The code you provided is just amazing its working smooth... Thanks a bunch man ..I was wondering if this macro could be run without clicking the search button ..I mean to say it costs me a trip to mouse to click and then I get data...I want this macro to run if I add few alphabets in cell A2 and data should be displayed is it possible ??

**mikerickson** · 07-30-2013, 02:30 PM

Have you looked at the AutoFilter feature. If you apply AutoFilter to your data range, it might do what you want.

**mikerickson** · 07-30-2013, 10:05 PM

I added some helper columns on a new sheet (Hide Me) to drive the dropdown list of unique company names.
Then I added a Change event to Result's code sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Call FilterChosen
End Sub

I think this will do what you want, but AutoFilter on sheet Data! would be more robust and stable.

**Khanzaki** · 07-31-2013, 01:50 PM

Hi mike,
Your attached workbook is not working now here are the list of errors I am getting
Capture.PNG Error.PNG Excel stops working.PNG
And regarding autofilter I don't want it to use I am happy with macros.

**Khanzaki** · 08-03-2013, 01:44 AM

Hi mike,
I made one change to the code you provided and now it's working wonders
Original:

Private Sub Worksheet_Change(ByVal Target As Range)
    Call FilterChosen
End Sub

New:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" Then
Call FilterChosen
End If
End Sub

The problem seems to have been solved but this only shows me the selected companies details ...What I need are criteria to resort the details of the selected company I know Autofilter are option but I don't want to go that way ....[Have a look on attached Searchable DataBase(mike).xlsb I tried using combobox and now its not working and if I use datavalidation it mysteriously works ]
The other attached file is found on web and I discovered this is what I need ... so can you please have a look on it ..and modify it according to Searchable DataBase(mike).xlsb
Thanks and regards

See the attached file below

**Khanzaki** · 08-16-2013, 11:38 AM

Bump no response

Hybrid View

Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Re: Creating a searchable database

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions