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?