Dropdown list with multiple conditions...

**skadieverwinter** · 03-20-2013, 03:48 AM

Hi there,

Sheet A: I have a database of samples which are all uniquely coded in column A. In column B it describes an integer for which box (IRL) the samples are stored in. In column C I have a Yes/No for whether the samples have been processed or not.
Sheet B: I have a summary of what IDs are in each box. so a row describes a box number and then a range of values for the IDs. I have been able to figure out how to get it to tell me the NUMBER of samples which have been processed (ie yes/no). I also need a dropdown menu that gives me the list of IDs (Sheet A) that meet the conditions of box (x) and processed no.

I also would like this to live update so that when I go into Sheet A and say that I have processed this sample, it will automatically shorten the list in the dropdown menu.

Any ideas? Please be gentle, I'm not a very experienced excel user...
Also, I am using Excel 2010.

Thank you!

**Spencer101** · 03-20-2013, 03:58 AM

I think an example workbook would be very helpful for us here...

**skadieverwinter** · 03-20-2013, 05:32 AM

Ahh, right. Sorry I thought my description would make sense.

Here is an example worksheet...

Yes, this one is generated on a different version of excel, but I will be doing the real work on the version I described above.

Thanks

**Kevin UK** · 03-20-2013, 06:24 AM

Hi skadieverwinter

Not a drop down list. If I get the gist of what you mean!
Return the ID if it meets the following: Box: Value. Contains: Between (6101-6135) etc. Complete: "No"
In C2 on the Boxes sheet, copy across and down:

For Excel 2010:

Formula:

Please Login or Register  to view this content.

For Excel 2007<

Formula:

Please Login or Register  to view this content.

Array formula: CTRL + SHIFT + ENTER

**skadieverwinter** · 03-20-2013, 06:37 PM

Hi Kevin,

I tried the formula in the example sheet, but it only shows me the one ID, but I need all the IDs listed that meet those criteria, and I don't want static cell references because I want to be able to add columns or sort the data... Also, from your explanation above it sounds like you wrote some conditions of being between certain ID numbers, which would be unnecessary because that should be covered by the box number criterion.

Sorry, I don't really understand the code you sent either, what do the "15,6,ROW" etc bits mean? I need to be able to translate what was designed for the sample spreadsheet for my actual dataset. Sorry, I am really very new to programming anything in excel so I am very stupid at it, so far.

Thank you.

**Kevin UK** · 03-21-2013, 05:51 AM

Hi skadieverwinter

As I said in post #4

In C2 on the Boxes sheet, copy across and down:

Put the formula in cell C2 (INDEX/AGGREGATE) and enter. Hover your mouse over the bottom right hand side of that cell untill the "+" appears, click and drag across (lets say column T), let go. Then click again and drag down 3 rows.

**skadieverwinter** · 03-24-2013, 09:33 PM

Hi Kevin,

Right okay that makes more sense. Sorry, I didn't realise that you'd done it so it listed the ids along the rows.
However, this is still kinda inviable because in my real dataset I actually have two separate categories (which need to be kept on the same line) for which I want lists of IDs for and they are all varying number (between 80 and 2 IDs)... Hence, why I needed a dropdown menu...

From what I can gather so far, drop down menus require a new worksheet to source the list from. Could I use your code and put each category (same logic used for each, but accounting for different processes) on a separate line in there (thus not obscuring the boxes database set), and then make drop-downs from that?

Thanks