Help Using 2 drop down to search Worksheet and populating another sheet

[pjginoo]

I have a lot of data saved onto a worksheet and would like to make it simpler to search and find things in it. What I am looking for is to use a drop down menu with several options that search the D column for matching data and then use a second drop down box that searches column C in the same worksheet that allows me to search within the results from the first drop down box to populate another worksheet in the same workbook. How can I do this?

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[pjginoo]

Ok here is a sample spreadsheet. I want to have two drop down menus. One which will allow me to search this list and only return a list of only for example, dogs. Then another that will let me go inside the search results that the first drop down gave me and give me only dogs with say black hair. All the meanwhile when the search is done it brings all the information along with it to the other sheet or even just hides the other pieces that do not match the search. Does this make any sense?

[NBVC]

In Sheet2, E3 add this helper column formula:

=IF(ISNUMBER(SEARCH('Sheet 1'!$A$2&"_"&'Sheet 1'!$B$2,A3&"_"&B3)),COUNT(E$2:E2)+1,"")

copied down.

Then in Shee1, assuming your drop down is in A2 and B2, for Type and Colour respectively, then enter a formula, in say D2 to give total count of matches:

=MAX('Sheet 2'!E:E)

then in to get the list of matches enter formula:

=IF(ROWS($A$1:$A1)>$D$2,"",INDEX('Sheet 2'!A:A,MATCH(ROWS($A$1:$A1),'Sheet 2'!$E:$E)))

copied down as far as you want and across the columns.

If somebody picks only Type or Color, then all Type or Color matches will show... when both A2 and B2 are inputted, the list narrows further... you can expand to filter for other column values too.

[pjginoo]

Ok I tried to convert your formulas to work for my spreadsheet but I have more lines than the example I made and it was finding errors and couldnt figure it out. here is a sheet more like mine. What I want to do is use a drop down to search for a type then only display that type then another drop down to show the distance within that type. I appreciate all your help but i think once I get the right formula in there I am good. Thanks again!

[NBVC]

So is my sample what you want as a result? Or did I miss the boat?

[pjginoo]

No that worked perfectly I just need to enter drop down boxes where you can select the type and distance to be selected. But yours worked well I just couldnt get it to work on my sheets because of the extra columns I had I was missing something causing some errors.

[NBVC]

See attached.

A1 and B1 of Sheet 1 contain the Data Validation drop down menus, the lists are in Sheet3.

[pjginoo]

For some reason when I applied that to my spreadsheet I get it to search by type and list the types then when I used the second box to select distance it does not give me any information or all the types with no consideration for the second boxs choice...

I have this code in sheet 2 along M:

=IF(ISNUMBER(SEARCH('Sheet 1'!$A$2&"_"&'Sheet 1'!$B$2,D2&"_"&'Sheet 2'!C2)),COUNT(M$1:M1)+1,"")

Then these codes in the sheet 1 data area:

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!A:A,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!C:C,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!B:B,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!D:D,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!E:E,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!F:F,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!G:G,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!H:H,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!I:I,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!J:J,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!K:K,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

=IF(ROWS($A$2:$A2)>$D$2,"",INDEX('Sheet 2'!L:L,MATCH(ROWS($A$2:$A2),'Sheet 2'!$M:$M)))

And this code in sheet one in D2:

=MAX('Sheet 2'!M:M)


Am I missing something? Your sample sheet worked fine but I cannot get it to work on mine...

[NBVC]

I am not sure, but 'Sheet 1'!$A$2 has to correspond to D2 and 'Sheet 1'!$B$2 has to correspond to C2. check those... and also the sheetnames.

What isn't working? Are you getting errors, blanks, duplicates?

You may need to attach another sample to diagnose properly.

[pjginoo]

When I have the drop down for the type selected it shows the type correctly but fills the entire sheet... Duplicating the last type and info from it to fill in the sheet. When I select a distance it gives me nothing. Even when there is info with those two selections. I checked those two formulas and they are corresponding to C2 and D2 on sheet 2... is the correct?

[NBVC]

If you select a Type with no Distance, it should list all the rows of that Type, selecting a Distance, should narrow the results to that combination of Type and Distance.

Check that the Distance format is the same in your Sheet2 and Sheet3 columns.

In Sheet2, select the Distance column, go to Data|Text to Columns and just click Finish.

In Sheet3 or where you have your lists for Data Validation population, select the list of Distances and repeat the Data|Text to Columns |Finish.

If that does not fix it, you need to post a sample workbook showing the problem.

[pjginoo]

I tried copy and pasting my info into your sheet and it doesnt work there either. I noticed that when I have just the type selected it has numbers in the M column like it is supposed to but when I select a distance there are no numbers in the M column.

[NBVC]

That is telling you that there is no exact match of the combination of the Type and Distance.

If you feel there is, then it is probably a formatting issue. Did you try what I suggested last?

Post a sample if you want better diagnosis... you can delete all columns except type and distance if that helps make it less confidential.

[pjginoo]

Yea I tried the formatting of the cells and it did not give me anymore results... Unfortunately all the data is pretty confidential. I gave you a sheet earlier that was really "dumbed"down to hide that and you got it to work perfectly and now I do not know what to do. Did all of the formulas I sent you look correct? Those were the only ones that I found in your sample sheet you sent me.

[NBVC]

Try changing the first formula to:

=IF(ISNUMBER(SEARCH('Sheet 1'!$A$2&"_"&'Sheet 1'!$B$2,D2&"_"&C2)),COUNT(M$1:M1)+1,"")

see if that makes a difference.

Like I said, you can delete all the information from the workbook. All I need to see is the info in the TYPE and DISTANCE columns and your Lists.