Create drop down list with range of values that is exact match from a table

Relatively novice with Excel.

Am trying to create a drop down list that does that following;

Returns a range of values (in this case all sizes) into cell F7 on sheet named Home! that matches the entry in cell C7 (item catalogue description) from an array on sheet Male NSN Catalogue! A:B (Column A the description and Column B holds all sizes).

To make it a bit clearer, the table on Male NSN catalogue contains an item called 'Light Blue Short Sleeve' throughout cells A2:A20 returning the respective sizes 31-49 throughout column B2:B20.

So when a customer selects 'Light Blue Short Sleeve' in cell C7 on the Home page the customer should then be able to select a size from the drop down list in cell F7.

I have done it this way because each size will have its own catalogue number (which I am competent enough to retrieve through simple use of VLOOKUP).

Many thanks in advance

Online Demand System.xlsx

That should be attached now.

As you will see I have attempted the data valiadation for drop down list in cell C7 (named 'Size') with incorrect formula use of INDEX and MATCH.

Thanks again.

Hello Glenn;
If I understand right you need the sizes appearing on Sheet "Male NSN Catalogue" Column B Cell B2 thru B21 to appear in you Drop Down List on Worksheet "Home" Cell F7.

If that's right all you have to do is in the Data Validation Window you have to select the Data Range as done in attached file.

Hope I have been of help to you.

Thanks & Regards

Mahesh Woolvara
Mumbai - IndiaOnline Demand System-WKM1.xlsx

Mahesh Woolvara,

Not quite. The drop down list in cell F7 is depandant on the item chosen in cell C7, which at the moment in this case is 'Light Blue Short Sleeve'

Think of the home page as an order form, where customers choose the item of clothing from the drop down list in cell C7 and then have to choose from the available sizes in the drop down list in cell C7.

The issue is that these sizes pertain to the item and are held on the Male NSN Catalogue worksheet (where I will be adding a lot more items, as well as a Female NSN catalogue worksheet). So therefore, I need the drop down list to be dynamic, the document attached contained my attempts at this using INDEX and MATCH formulai, but to no avail.

Have I explained it clearly enough?

Once again, thank for yours and anyone elses assistance.

I've tried what you have linked and it only works for if the item is 'light blue short sleeve'. Can't see where the forumla makes the reference to sheet1 that you created either.

I need in effect a VLOOKUP that picks up all sizes in each row for where the description matches in the sheet Home! in the worksheet named Male NSN Catalogue. I am also going to have to amend the formula you are able to find for me to contain an IF function; If cell D4 is male or female (because I will also be adding a Female NSN Catalogue worksheet from which to pull female sizes).

Sorry to be a pain.

Thank you for your response.

I checked what you did and it did what you said it did. Problem is, i'm only a little familiar with the data validation, defining names and the data manager, and I'm sorry but I am not competent enough to understand the formula you used. I saw the DVs you did for each item and where the formula referenced (they went into sheet1 and worked their way across the columns).

What I really need is a multiple VLOOKUP against the clothing description i.e. Light Blue Short Sleeve (if selected in cell C7) into the description on the Male NSN Catalogue worksheet (column A:A) and return all possible sizes (column B:B).

Think of the Home sheet as an order form for customers, they will not have access to or see any other worksheet.

I have uploaded the latest version of my document and as you will see I have added more items into the Male NSN Catalogue Worksheet and I still, potentially, have to add hundreds more, so, creating DV for each and every item I add to the catalogue is going to be a pain, add to that, some items may be removed in the future.

To complicate matters, I will have to do the same for female sizes. The choice in cell D4 will determine where to look for the sizes using an IF function followed by the formula that I am seeking your help for (one step at a time though).

As ever, thank you for assistance, and no doubt persistence and patience, it is appreciated. I will be in your debt.

Online Demand System (1).xlsx

Wow that's looking more like it!! Looks great!

Now if we can squeeze the sizes into a drop down list it would be perfect! I tried copying and pasting your formula into a DV value but it doesn't like it. Says it needs to be delimited to a single row or reference.

The reason for the need of a drop down menu is that, row 7 is the means to select information for customers to order a single item. I wish to allow, say 9 more rows immediately underneath, in which the customer can order more items i.e. row 7 for a light blue short sleeve shirt, and then, row 8 for if they want to order say trousers as well etc.

Functionality wise it does exactly what I need it to and thank you for going that extra step further with the *** and catalogue number conditionals. I have studied your formula so as to learn of its use and application. That formula opens up more possibilities for me should I need to redirect other information from the item catalogue in the future.

Once again many thanks!

What you have provided is perfect and I can work around it by hiding the column F:F with the formula you have provided and then doing an adjacent column G:G with a DV list referring to F:F from which the values can be selected. Perfect.

Many thanks for taking the time to write the formula and for educating me in its use! This can now be considered solved.

Added to reputation and marked as solved. Many thanks again.