Creating a Dependant Drop Down List from data in Multiple Worksheets in the same Workbook

I have a workbook with 9 worksheets each of which contain in column "B" a unique list of Items relevant to the sheet name e.g. Band, Face, Bag, Stone ... Each sheet containing various number of data rows. What I am wanting to do is to create a dynamic dependant drop down list in a new sheet containing the lists from all the 9 sheets. I have created my first list in a "Lookup" worksheet and gave it a Named Range : "Item" containing the 9 worksheet names. In another sheet at cell "C22" I have created a validation list referencing the named range but can not work out how to link a selection from the drop down list to the respective worksheet range and column "B".

Is this at all possible and if so where to from here or is there another approach that needs to be taken?

I have Excel 2010. It sounds like you're making this harder than it is. I was able to create a drop down which referenced the drop down results in another series of cells. Can you attach your spreadsheet here? To create the drop down list I went to the Data tab, the Data Tools subsection, and then clicked on data validation twice. Under "allow" I selected "List" and then under "Source" I selected the cell range into which my other drop downs showed their answers. My drop down options are the answers from the other drop downs. I have uploaded a picture and you can see my new drop down on cell P11. The contents of it are from cells H11:H31. Hopefully this helps but if not then please upload an image or the spreadsheet itself

Thankyou worthm. Unfortunately I can not view the images and from reading other issues listed on the forum there appears to be a universal problem reported on this matter. Have tried to load the workbook however it's too big, so will try and image.

To further clarify what I am trying to achieve in cell C14 I have my first dropdown list containing the nine "Items". When I select "Bag" from the list the 2nd drop down should show the list from column B in the "Bag" tab at cell D14, similarly if I select "Face" from the 1st dropdown then the list from column "B" in the "Face" tab should be visible in D14. I have been successful in creating the 2nd dropdown list but only for one of the 9 items in the 1st dropdown where under source I have referenced it as =Bag!$B$3:$B$31. The question is how to have the "Source" recognise the various other worksheets and column ranges.


DropDown.jpg

I don't feel like I have enough information to proceed. Are you trying to accumulate or count up all of the other drop down list selections? What are you trying to do with the data? And an upload or a picture of the actual sheets in question would be very helpful

Attached is a condensed version of the workbook. The INVOICE tab is where is wanting the drop downs to appear under the sub-headings "Item" (1st drop down) and "Item Description" (dependant drop down). In the cells C14:C27 the 1st drop down list (named range "Item") should be available to select from the Item List. Then in cells D14:D27 a dependant drop list is to display the "Item Description" for the selection made in the 1st drop down. My apologies if I'm not making myself clear it's all new to me and am trying to get a grip of the terminologies and lingo as I learn from my experience, thanks.

Thank you Timeout, that helps

This website provides step by step instructions. I am working on turning their tips into your spreadsheet but this will help for now

http://www.excel-easy.com/examples/d...own-lists.html

I wasn't able to get these tips to work but I'll keep trying

Thanks...I was able to find these tips useful and I had a working document using these tips however it required all the source data be held on the one worksheet (lets call it "lookup" for now) with a column for each sheets "Item Descriptions" with the header column name similar to that of the tabs. This meant I would need a macro to update the "lookup" worksheet every time a change to the "Items Descriptions" column (ie "B") in any of my 13 source worksheets occurred. This then lead me to believe I was trying to achieve something that is not possible. If all the source data for the drop downs need to be on the one sheet then this seems to defeat the purpose trying what I though should be a straight forward creation of some drop downs? Still trying things though...

http://www.excelforum.com/excel-gene...alidation.html