Creating a multi criteria drop down

**tanksalevikrant** · 12-13-2010, 03:26 AM

I have attached a file for your quick reference where I need some help.

This is a job I am creating whereby I need to choose any of the item from dropdown which will have some number of criteria further to select. Upon selection of such crtieria, the requisite rating (as mentioned in the sheet) should populate to the user.

E.g If point "Sales Value" is selected it should give the user options from cell B2 to B5. If B2 is selected then it should populate Rating 1 i.e. Cell C2.

Is it possible ? I read of creating combo box but not sure if this is the solution. Appreciate your kind help in this or some other simpler way, as I have around 50 main points that follows multiple criteria to choose from.

**teylyn** · 12-13-2010, 04:05 AM

Hello,

see attached for an example. I've used dynamically defined ranges for the secondary drop down, and then a vlookup to show the rating.

With the primary drop-down in column I, the secondary drop down is defined (with the active cell in J2 when you edit the range name):

=INDEX(Sheet1!$E:$E,MATCH(Sheet1!$I2,Sheet1!$D:$D,0)):INDEX(Sheet1!$E:$E,MATCH(Sheet1!$I2,Sheet1!$D: $D,1))

This solution is dynamic, i.e. you can have as many primary items as you want, but you must ensure that the table for the secondary drop down is sorted in ascending order by "Point". Of course, you can also have the drop-down cells in another sheet. You only need to adjust the references to Sheet1!$I2

Also note that the "point" column should not contain any blanks, otherwise the lookup for the secondary drop down will not work.

If you have any questions about this, please pipe up.

cheers

**tanksalevikrant** · 12-13-2010, 07:02 AM

Thanks a ton ! One more Q there >> How do you eliminate the "#N/A" remarks ?

**tanksalevikrant** · 12-13-2010, 07:29 AM

And I need some reformatting in the structure as in the attachment.

Vertically there are some business areas and horizontally are the possible impacts. Hence while in cell B3 I need a drop down only related to Sales value and C3 to show drop down that associates only with B3 dropdown. Same to apply in other columns.

**teylyn** · 12-13-2010, 03:00 PM

And I need some reformatting in the structure as in the attachment.

Did you mean to attach a new version?

**tanksalevikrant** · 12-14-2010, 01:22 AM

Originally Posted by teylyn

Did you mean to attach a new version?

regrets ! missed that and later struggled a lot to have that uploaded , somehow it was not working, finally got thru.

As is evident in the worksheet, against each of column A values I would map the associated criteria in B, D, F.... and against each criteria I would map rating in column C, E, G.

Each kind of impact area e.g. sales value, inventory value (i.e. row 1) has only particular criteria to map. Sales value has criteria to evaluate in values only --> < 1 mio, > 1 Mio and not High, Moderate, Low. Thus under sales value only those need to populate thru the dropdown. Whilst the associated rating expected are 1,2,3...

Thanks in advance.

**tanksalevikrant** · 12-14-2010, 03:03 AM

The upload seems to give me a problem over and over .

**teylyn** · 12-14-2010, 05:30 AM

Hello,

put your cursor in B3 and define this range name

criteria =INDEX(Sheet1!$N:$N,MATCH(Sheet1!B$1,Sheet1!$M:$M,0)):INDEX(Sheet1!$N:$N,MATCH(Sheet1!B$1,Sheet1!$M: $M,1))

put your cursor in C2 and define this range name

rating =INDEX(Sheet1!$O:$O,MATCH(Sheet1!B$1,Sheet1!$M:$M,0)):INDEX(Sheet1!$O:$O,MATCH(Sheet1!B$1,Sheet1!$M: $M,1))

Use data validation list with =criteria in columns B, D, F, H, J
Use data validation list with =rating in columns C, E, G, I, K

The table in columns M to O must be sorted ascending by the values in column M for this to work.

Since the range name uses relative column references, it is important that you are in the correct column when you define and/or change it. Otherwise, you'll see some unexpected results.

see attached.

**tanksalevikrant** · 12-14-2010, 07:04 AM

Thanks once again for your kind assistance. It does work as intended, except for one rule I was seeking to be enforced in the column "Rating".

E.g. If the "Criteria" column contains value "High" then corresponding "Rating" column should pickup value or only dropdown value "4" and not give option to the user to select 1 or 2 or 3 or 4 or 5 - where the file user can make a mistake by choosing wrong one.

**tanksalevikrant** · 12-14-2010, 07:11 AM

Originally Posted by tanksalevikrant

Thanks once again for your kind assistance. It does work as intended, except for one rule I was seeking to be enforced in the column "Rating".

E.g. If the "Criteria" column contains value "High" then corresponding "Rating" column should pickup value or only dropdown value "4" and not give option to the user to select 1 or 2 or 3 or 4 or 5 - where the file user can make a mistake by choosing wrong one.

While requiring to satisy the above condition I am preferably looking at a solution other than vlookup.

**teylyn** · 12-15-2010, 03:17 AM

Can you detail that, please?

I'm not sure this will work with data validation, since data validation with dynamic ranges only takes a limited amount of calculation.

You may need to set up individual data validation for the rating cells that depend on High/Medium/etc. with a simple IF statement in a custom validation.

If you don't want to use a Vlookup, what then is your intent?

**tanksalevikrant** · 12-15-2010, 06:17 AM

Originally Posted by teylyn

Can you detail that, please?

I'm not sure this will work with data validation, since data validation with dynamic ranges only takes a limited amount of calculation.

You may need to set up individual data validation for the rating cells that depend on High/Medium/etc. with a simple IF statement in a custom validation.

If you don't want to use a Vlookup, what then is your intent?

Ok no problem. Actually I was wondering if a solution other than vlookup is available. Yes, IF condition can be applied.

Thanks, once again for kind help.