Drop down lists in

QuestionEdit

QUESTION: I am using2002. I would like to ask two related questions :- I am trying to link 2files, The first being daily manufacturing downtime results, and the second one being a monthly report giving the bigger downtime picture.

1) In the firstfile I can easily create a drop down list (ddl), but what I want to do is after selecting something in the first drop down list have the consecutive cell to have another drop down list but linked to the first one i.e. if i selected the Paint machine in the first ddl the second cell ddl would only show downtime problems associated with that machine, but if i selected rivet machine, then the second cell ddl would change to show only the rivet machine issues . If the above is possible, is it then possible to have say 10 rows containing those ddls (to cover a few downtime issues that can happen in one shift )and then the operator can choose the right downtime category each time and in a separate cell far right put a figure say 60 for the amount of minutes the machine was down

2) In the secondfile(the hard bit) I want to pull the downtime minutes totals from the first sheet drop down lists so that the downtime information goes into the correct section for the monthly report Hope this makes sense Thanks for your time Nigel.

ANSWER: 1) This is what's known as dependent or cascading dropdown lists. Very cool and very powerful, but setting them up involves a number of steps and advanced techniques. Below are a couple of links to very detailed explanations of how to set them up:

http://www.ozgrid.com/Excel/dependent-lists.htm

http://contextures.com/xlDataVal02.html

You can apply this technique to as many cells as you want.

2) If I'm reading this right, you can total the minutes in the first file and then directly link them to a cell in the second file. Type and equal sign in the desired cell in the second file and then navigate back to the target cell in the first cell, click and hit enter establish the link. Let me know if I'm off base on this.

---------- FOLLOW-UP ----------

QUESTION: Reading my question back it didn't really clarify what i wanted to happen on 2nd file, and I'm not sure if i can make it clearer but I'll try :- I need to enable the 2nd file (This has maybe 60 different cells for each of the downtime issues that are contained in the drop down lists on the first file) to pull the right downtime minutes for each issue from the lesser (maybe 10) cells showing the actual downtime that happened on each shift. So each of the 60 cells needs to check the first file to see if the downtime minutes in the 10 cells apply to it or not as these will change depending on choices made in drop down lists. I have attached an image to help, hope this is more understandable?

AnswerEdit

Nigel,

I think you will better off setting up a equivalent File2 table in File1 and then reading the results with direct cell links rather than having File2 do the selective adding up. You can use the SUMIF function to get the totals, something like:

=SUMIF($J$9:$J$50,"1. Losing Origin",$J$9:$J$50)

This will be much neater, cleaner and easier to maintain than having the same SUMIF formulas with full workbook addresses.

Doug