Creating a multi criteria drop down

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.

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

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.

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.

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

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.

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.

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.