Thread: Multiple Search Results of Multiple Criteria

I was given another solution using a macro to pull up the results.
Below is the link to the other post in another forum.
http://www.mrexcel.com/forum/showthr...90#post2249290


Hello,

I am trying to create an Excel workbook with a search worksheet. I have a worksheet that has data entry and would like to be able to filter the results based of the selection criteria that is chosen in the "Search" worksheet.

There is a tutorial that I was following which does exactly what I want to do but I cannot get mine to work. Can anyone help me fix my errors to get the search to work.

Any help would be greatly appreciated.

Thanks.

Here is the linked of the tutorial I was following.
http://www.get-digital-help.com/2009...ormula-part-4/

Hello,

I am trying to create an Excel workbook with a search worksheet. I have a worksheet that has data entry and would like to be able to filter the results based of the selection criteria that is chosen in the "Search" worksheet.

There is a tutorial that I was following which does exactly what I want to do but I cannot get mine to work. Can anyone help me fix my errors to get the search to work.

Any help would be greatly appreciated.

Thanks.

Here is the linked of the tutorial I was following.
http://www.get-digital-help.com/2009...ormula-part-4/

The link was mangled by the forum software. Use the link icon to enter long links.
Can you possibly post a smaller file? And zip it before uploading? Not everyone here is on a fast connection or an unlimited data plan.

cheers

Hi,
there are several problems:

- some of your search ranges do not contain any data and will return arrays of errors without a single valid result. This will break the formula.
- other search ranges contain empty cells that will evaluate to errors, because they do not have a specific data type
- Your index formulas in row 6 are the wrong way round. Instead of

=INDEX($D6,Image_Data)

it should be

=INDEX(Image_Data,$D6)

The same for all the other columns in that row.

The ISNUMBER test returns FALSE given the SEARCH will return a #VALUE! error where source values are blank (even when criteria is blank).

The blanks are present in the search ranges for two reasons

1 - you have legitimate blanks (eg Part Type)

2 - all your search ranges include a row of blanks given they commence from row 2 but their height is determined by count of non-blanks within the column - this count will include the header.


So, first you will need to account for the legitimate blanks within the Array by appending each string being searched, eg:

SEARCH(Search_Shelf_Number, Shelf_Number_Data&" ")

To correct the named range issues you should adjust the range accordingly - ie subtract 1 from the COUNTA.

However, if you adopt this search technique then IMO you should look to use INDEX rather than OFFSET to create the Dynamic Names.
Using OFFSET the Arrays in Col D on SEARCH sheet will all be Volatile by association and they are relatively expensive formulae in their own right.

My suggestion...

First - given the height for all ranges will be common to all I would store this is one name:

Data_Rows
=COUNTA(LIST!$D:$D)

Then, for ex.:

Name: Shelf_Number_Data
RefersTo: =LIST!$B$2:INDEX(LIST!$B:$B,Data_Rows)

instead of

RefersTo: =OFFSET(LIST!$B$2,0,0,COUNTA(LIST!$D:$D),1)

and so on and so forth for all names.


Once you adjust both for blanks you should find your SMALL Array works correctly (though again to reiterate I would avoid the Volatile Names).


You still need to correct your resulting INDEX formula given they are constructed incorrectly... ie:

=INDEX(SAP_Number_Data,$D6)

not

=INDEX($D6,SAP_Number_Data)

the latter would return a #REF! error.

Originally Posted by bbarrene

Could you please explain to me as to what you mean by "volatile by association". Also, by "relatively expensive formulae", do you mean that these formulas require a lot more memory?

For more info. on Volatility see the link in my signature.

Arrays (along with SUMPRODUCT) are more "expensive" than non-array formulae given the way the functions are processed.
It follows that if said Arrays are Volatile then they are potentially calculating more often than is necessary and in so doing using more resources than would otherwise be deemed ideal.
Avoiding Volatile Arrays is generally a good idea for this reason - wherever & whenever possible.

Originally Posted by bbarrene

Do you mean I should change the formulas to include OFFSET rather than INDEX or should I just delete the Names altogether. My guess was that I should just change the formula. Could you please verify?

I meant that you should revise all of your "data" names to use INDEX rather than OFFSET thereby removing the Volatility from the Array - you appear to have done this.

Originally Posted by bbarrene

First, I wanted to hide the #NUM! cells that appear if the search results do not reach that row.

IMO you would be best served conducting a one off calculation to determine the number of search results that are to be generated based on selections.

The reason behind this is that you can reduce the number of times you process the Array - ie only process when necessary.

The below would tell you how many records are to be returned:

Code:

A4: 
=SUMPRODUCT(--ISNUMBER(SEARCH(Search_Shelf_Number,Shelf_Number_Data&" ")),--ISNUMBER(SEARCH(Search_SAP_Number,SAP_Number_Data&" ")),--ISNUMBER(SEARCH(Search_Title,Title_Data&" ")),--ISNUMBER(SEARCH(Search_Part_Type,Part_Type_Data&" ")),--ISNUMBER(SEARCH(Search_Plant,Plant_Data&" ")),--ISNUMBER(SEARCH(Search_Machine,Machine_Data&" ")),--ISNUMBER(SEARCH(Search_Station,Station_Data&" ")),--ISNUMBER(SEARCH(Search_Drawing_Num,Drawing_Num_Data&" ")),--ISNUMBER(SEARCH(Search_Drawing,Drawing_Data&" ")),--ISNUMBER(SEARCH(Search_Room_Num,Room_Num_Data&" ")))

You can then use this value to prevent unnecessary calculations with the Array and in turn handle the #NUM errors appropriately, eg:

Code:

A6:
=IF(ROWS(A$6:A6)>$A$4,"",SMALL(IF(ISNUMBER(SEARCH(Search_Shelf_Number, Shelf_Number_Data&" ")*SEARCH(Search_SAP_Number, SAP_Number_Data&" ")*SEARCH(Search_Title, Title_Data&" ")*SEARCH(Search_Part_Type, Part_Type_Data&" ")*SEARCH(Search_Plant, Plant_Data&" ")*SEARCH(Search_Machine, Machine_Data&" ")*SEARCH(Search_Station, Station_Data&" ")*SEARCH(Search_Drawing_Num, Drawing_Num_Data&" ")*SEARCH(Search_Drawing, Drawing_Data&" ")*SEARCH(Search_Building, Building_Data&" ")*SEARCH(Search_Room_Num,Room_Num_Data&" ")), ROW(Title_Data)-MIN(ROW(Title_Data))+1), ROWS($F$5:$F5)))
confirmed with CTRL + SHIFT + ENTER
copied down to A225

Using your sample file where given existing search terms the SUMPRODUCT in A4 will generate a result of 2 we're now calculating the Array only twice (rows 6 & 7) + 1 SUMPRODUCT rather than calculating the Array 220 times as was previously the case.

In the above we're returning a NULL ("") where there is no search result - this will still not handle the errors in the INDEX results in the remaining columns... to cater for those we must adjust each formula based on the value in Col A, eg:

Code:

C6: =IF($A6="","",INDEX(Image_Data,$A6))
copied down

With the above IF construct being repeated for each column in results table.

Originally Posted by bbarrene

Second, in the column titled "Image", there will be a hyperlink to a PDF file that will contain all of the information in that row as well as a picture of the part. I thought the hyperlink would be accessible to click on in the SEARCH worksheet but it is not. Would it be possible to make it so that a person could click on the hyperlink in the SEARCH worksheet rather than having to change to the LIST worksheet to open the PDF file. If it is, could either of you help me with the coding?

Without seeing the links it's hard to give any specific advice - depending on the text returned by the INDEX you may find you can encase within a HYPERLINK function to generate the link - eg:

Code:

C6: =IF($A6="","",HYPERLINK(INDEX(Image_Data,$A6)))
copied down
(there is optional parameter for displaying a "friendly name" in the cell rather than the link address itself if so desired)

If you need further assistance on this post back with an updated version in respect of all of the above.

SORRY for the long reply. I wanted to make sure I included all of the information this time...

Ah. Now your volatile explanation makes more sense. I guess I should have read your suggested reading before asking that question. Sorry about that.

So since you have helped me this far, I guess I should explain as to what I am trying to accomplish with this EXCEL file.

I want to make an Excel Database where I could create a "DATA ENTRY" worksheet (as shown in the attached Excel worksheet) to auto fill information into the "LIST" worksheet.

My vision is that once the "Submit" button is pressed on the "DATA ENTRY" worksheet, all of the information would populate the fields of the next available row in the "LIST" worksheet. For example. If I was to enter data and press the submit button in the "DATA ENTRY" worksheet, Row 173 in the "LIST" worksheet should auto-fill with the data that I have entered since it is the next available empty row.

I am not sure if that is asking for more than Excel is capable of doing but I have not really explored this option since I have been focusing on getting the "SEARCH" worksheet to work first.

So, to get back to the "Search" worksheet and clarify what I wanted to do with the Hyperlink. In the "LIST" worksheet, I have hyperlinks to files that will be inserted into the "IMAGE" column of the worksheet. (I have put an example "NUT RING.xls" into row 2 for you to see.).

These hyperlinks will need to be accessible to be clicked on by user in the "SEARCH" worksheet. Now, I believe I have accomplished this with the last code that you suggested in your previous post.

I initially had the hyperlink as an adobe file but I have changed it to an .xls file because I would like that hyperlinked file to be able to autoupdate if changes are made to the data of the row it is referring to.

Let's call the file that is hyperlinked a "Library Card".

The EXCEL Template file I had created for the Library Card was working when I first started creating the EXCEL Database file but for some reason, it is not functioning properly anymore. If you open the Nut Ring.xls file, you will see that the information auto-filled but it is referring to the wrong row. I am using the LOOKUP function to auto fill the fields.

The process I am using to create the Library Card is to open the EXCEL file "Library Card Template.xlt" and just paste the title cell of the row I want to populate the data from. Once entered, the other cells would automatically fill in. From there, I would be able to insert a picture above the information to allow someone to see what the part looks like. I was hoping to be able to insert the Placeholder that PowerPoint has (as seen in the picture on the right in the template file) but I have not been able to do this nor do I know if it is possible from what I have researched. Once the Library Card is complete, it would need to be saved and renamed and then inserted into the "IMAGE" column in the row that it is collecting its data from.

This was the best option I could come up with to fill in the Library Card and mimizine the duplicate work of having to retype all of the information all over again. If there is a faster and easier way that you can recommend, I would greatly appreciate if you could inform me of it. One problem that I could forsee already with my way of doing it is the problem of dealing with two parts that have the same title but different information (i.e: Title - CHAIN BOSCH KLINE in rows 30 and 31).


Thanks again for all your help.

Originally Posted by bbarrene

So since you have helped me this far, I guess I should explain as to what I am trying to accomplish with this EXCEL file

As a general rule we ask you stick to one predominant question per thread.

Reasons for this are threefold:

1 - it helps others searching for similar solutions

2 - because those answering do so based purely on the initial request and may prefer not to nor indeed be able to answer latter and technically "unrelated" questions.

3 - ensures you get maximum exposure on the board - if people see a thread with multiple replies they tend to ignore assuming it "in hand". Should the last post be in effect a brand new question you lose the chance of their participation.


In this thread for example we're really only concerned with data retrieval based on search criteria - and subsequent handling of the hyperlink data.

Regards the hyperlink - it sounds as though you've resolved that, correct ?

Originally Posted by bbarrene

So, to get back to the "Search" worksheet and clarify what I wanted to do with the Hyperlink. In the "LIST" worksheet, I have hyperlinks to files that will be inserted into the "IMAGE" column of the worksheet. (I have put an example "NUT RING.xls" into row 2 for you to see.).

These hyperlinks will need to be accessible to be clicked on by user in the "SEARCH" worksheet. Now, I believe I have accomplished this with the last code that you suggested in your previous post.

I can't verify you've resolved this matter because the "list" file attached in the zip won't open on my laptop - in fact it sends XL into meltdown every time ...


Regards your other questions pertaining to use of template files etc please create a new thread as outlined above - this is for the benefit of all.

Thanks,
D.O.