drop down boxes to filter a list - I need some pointers

[RobShaw]

Apologies if this issue has been already dealt with i am new here.

I am trying to paetly automate a process we hve to deal with at work every day. We are issued product catalogues in which all our current product range is stated along with the cost, rrp, code number etc. in the following form:-

Model - Accessory - Code - RRP - Cost

The model column contains one of about 30 possible models and is repeated several times, ( I know access would be ideal for this task but I haven't got it at workand it aint gonna change any time soon)

There are a set of accessories for each model say around 30 idividual items (which can be repeated for different models.) I'm not going for efficiency here btw.

I need to use a feature very similar to autofilter, using drop down boxes in seqwuence, I guess what I'm asking is can I control the source list of a drop down list based on the contents of a previous list.

So for example I select a model - model001 and I need the list of options in a second list to be only the ones corresponding to that model.


The reason auto filters don't work is that I need to put a control button on the sheet that copies the selected line into a list that can be printed out and summed etc. seperatly form the source list. also I need to protect the source sheet from modification.


Can anyone point me in the direction of some exaples or a similar application, I am reasonably profficient with excel but obviously not experienced wnough to know of a way round this problem.

Thanks in advance
Rob

 

[SteveO59L]

Have a look at Data Validation

 

[RobShaw]

I know how to create a dependant list using data validation in excel, what i then need to do is look up from the catalogue the product code, rrp, and cost associated with that selection, It is i suppose a double index

I am trying to use vlookup, but it will only let me match on one index, I can't seem to find a way to nest them.

Is there some function that will take match multiple columns and return a single cell?

thanks again