Returning Multiple Values as Dropdown List

Hi,
Actually I plan to create an eRequisition Form for our branch sites. My focus would be the consumable items of Lexmark printers. I want to create cascading drop-down lists based on data table (worksheet : All) as per attached. From lots of examples I've seen in this or other similar forums, I notice that in order to allow this cascading to work is by creating column with header name identical with the input in the 1st List. But I'm thinking, if I need to do that, then there gonna be hundreds or maybe thousands columns to create as the number of branches are expanding, so I think tht should not be practical.

I found examples that shows how lookup 1 value can return multiple values but I totally cannot think of a way to allow those multiple values to be returned as dropdown list rather than displaying all in rows of the worksheet.

What I need is that, in 1st list, upon selecting the branch, it'll populate all items related to the branch and when selecting the item, it'll populate brand/model list for the particular item only. The input in the dropdown list should not have duplicates. Been trying using pivot to do this with macro that enables auto refresh once any changes made in the table.

Is there anyone can help me to create this cascading drop-down lists just based on the table as per attached? I really want to avoid having to create extra tables to allow this cascading to work.

Hopefully I clarified my situation clearly and there would somebody can help me out.

https://www.dropbox.com/s/nrueipbu2t...INTERKUv1.xlsm

Hi Sixthsense,

Tqvm for the links. The 3rd one (http://www.contextures.com/xlDataVal13.html) is relevant to my needs. Just that, right now I have another situation. After managed to populate the 2nd drop down list based on the matched branch, the 2nd dropdown list has duplicates. I need to ensure that it'll only have unique values though. Is there any script/formula I need to add in to the data validation list to allow this, please? Or if it's not possible to do in Data Validation list formula, any VB script, pls? I have spent the whole day today trying to research any example..seems cannot get the one that can help me out

Tqvm

Dear Sixthsense ,

Herewith I attached the workbook I've worked on after you gave me the link for guidance. Please take note that worksheet "ALL" consists of all the list of data as the main source, while worksheet "ORDER" is where I apply the cascading dropdown list data validation list.

Following the link and sample you gave earlier, the duplicates I found is for ITEM column. Please help me on how to pass this output in another place before I can populate the list. After that is correctly populated, the selected input from column "BRANCH" and "ITEM" should be the conditions to populate the list in the 3rd col named "MODEL".

Thank you very much.

Does this do what you want, if so Perhaps you File could be altered accordingly.

Hi MickG,

Thank you very much for the effort to do this for me using macros.I could see that it works perfectly but only for first row. When I expand down the validation list scope, the input for Item list and Model list are still following those from the first row. I need to have it to work on every row to allow the Requester to submit their requests for other branches as well.

I'm very sorry I don't know how to alter your macro scripts to rectify this, so I need to refer to you back. Or, maybe, for anyone of you out there who can suggest alternative(s) to do it only using script at validation lists, it is much appreciated.

Thank you very much for your co-operation.

-Dahlia-

Hi MickG,
The attached file herewith is the amended copy with real data content and tested to expand on 2nd row for your reference.

I will look at your file in due course, but I am not sure what the exact problem is.
I am attaching a Modified version of my file showing, from column "G" on, All the Basic "Branches" against the related "Items" and "Models". If the related Items are not what you want Or the Code is not producing them in the Validation Lists. Please give an example of the data thet you would like to see from a specific Branch/Item/Model in the data.

Hi MickG,

Actually, what you've done for me for the first file was totally correct. The amended one is not actually necessary. But the issue I'm facing with the first file is that it only works for ROW2. Probably I have not explained it clearly earlier. So, please allow me to clarify by referring to my further explanation in the below attached file for your kind perusal:-

I didn't realise there where multi Validation cells in each column.
This attached File should now address that.
If this now, does what you want, and you would like it to work in you own file , send me a copy, or if its the same as you previous file , tell me where you want the validation cells and the sheet "All" added code data, and I will modify to suit.

Hi MickG,

Thank you very much for your input. I've tested it. I was so happy and excited at first. However, it suddenly stops running correctly after that. Then I exit excel and reopen again. worked for a while or sometimes not work even after restart excel. I'm not quite sure what happen. Let me run some more tests and revert to you on my findings or the patterns that contribute to the problems. Just FYI, am not sure if this info is relevant but, am using MSoffice 2010 Professional on OS Win-7 Enterprise on 64-bit machine. The last time I used an Autodesk program on 64-bit machine, I notice that some features in it not running stably.

I'll try testing ur file on 32-bit machine and let you know.

Thank you very much for your co-operation.

-Dahlia