Data validation depending on other data validation without named lists

**Stormbringer** · 01-18-2015, 04:22 PM

=== FOR THE ACTUAL PROBLEM, SEE POST 7 FOR THE EXCEL FILE ===

I have a long list of products in a table, where new products are added regularly. In another sheet I want to be able to retrieve this list, let's say in cell X. However, since this product list is so long, I want to sort by category of these products first. So I'd like to select the category first, in cell Y, to narrow the products list in cell X.

Please Login or Register  to view this content.

In the example above I want to have a list of "A","B","C" in cell Y. If I select category "B", cell X should give a list of products "4","9","10".

An array with something like INDEX(B2:B11,MATCH(cell Y, A2:A11,0)) for the data validation comes in mind, but I can't get it to work. I know I can make a separate column for each category and make named lists in combination with INDIRECT, but that would be pretty cumbersome. So the question is, can I make a data validation list for cell X, depending on one column as in the example?

(And less important, but is it possible to make a automatic list of unique names in cell Y depending on the first column? I know it's pretty easy to make a list in the conventional way, but I like to have everything automated. So that if a category name changes, or a new product with a new category is added, the list of cell Y would change automatically.)

**oeldere** · 01-18-2015, 04:26 PM

Why not use a filter to get the result?

**Stormbringer** · 01-18-2015, 04:31 PM

I'm using it on a different sheet for an invoice. Also, I need several rows with different categories and their products.

**protonLeah** · 01-18-2015, 05:05 PM

It would be better if you put each category in a separate column as a header with the products in rows below.

**Stormbringer** · 01-18-2015, 05:07 PM

I know, I can make named lists then. But separate columns will get me in trouble for other things. Would it be impossible with one column?

**oeldere** · 01-19-2015, 04:05 AM

I think protonLeah meant you can use index / match (index / vergelijken in Dutch) to find your data.

You get better help if you add an excel file without confidential information.

Please also add the desired (expected) result manualy in your file.

**Stormbringer** · 01-19-2015, 07:59 AM

Ok, a solution the actual file would be even better, I hope it's not too much effort. INDEX/MATCH I already use a lot, but I'd like to make some processes more user friendly with Data Validation Lists.

Please see the file here!

I'll try to explain the process. There are 5 sheets in the Excel file. It's meant to create a database of all Customers and their Orders, where the Orders are comprised of a table of Slips ("Products"). Also there's an "Invoice" which gets the data of a particular order on one page to be sent to the customer.

Slips and Customers are just a database with information. When a Client makes an order, a new OrderID is made in the sheet Order and assigned to a Customer. Then, in OrderSlips, the Slips that the customer ordered are being assigned to this new OrderID. This information is later used to calculate the total price and to show the ordered Slips in the invoice automatically.

PROBLEM 1: As you see there are a 100 slips, and this number will grow. In OrderSlips, these Slips must be entered for a particular order, but the many possibilities make it cumbersome as the list for data validation is gets very long. So I would like to make a selection (from a data validation list) of the Category of the Slip first. Depending on this Category, the list of the Slips should be narrowed down. That would make the process of selecting the Slips easier. The two columns where I want to make two dependent data validation lists are painted ORANGE.

PROBLEM 2: This is almost identical to the problem above, on a different place. To make an invoice, the particular Customer is selected in cell B8 in Invoice. Then an Order of this Customer is selected in cell C20. As nobody knows these OrderID's by heart, a list of all the orders of the in cell B8 selected customer are listed in the columns H to K. That makes selecting the correct order easier.

But as in the first example, it would be much nicer if selecting the OrderID in cell C20 would be just a data validation list, which depends on the selected customer in cell B8.

I hope the provided file will make things clearer. Other tips for make a more streamlined workflow are also welcome of course! Enormous thanks in advance!

**kvsrinivasamurthy** · 01-19-2015, 11:22 AM

Pl see file.
Using Worksheet_SelectionChange event Data validation is done for column B and C of sheet "Orderslips".
If no selecton is done for cell B column No list s generated for C column cell.
Any clarficatons welcome.

**Stormbringer** · 01-20-2015, 11:24 AM

Wow, that's great! Many many thanks!

However, it gives a lot of errors in Excel for Mac (but seems to work fine anyway). If someone has a solution with "regular" formulas, that would be great!

Data validation depending on other data validation without named lists

LinkBack

Thread Tools

Rate This Thread

Display

Data validation depending on other data validation without named lists

Re: Data validation depending on other data validation without named lists

Re: Data validation depending on other data validation without named lists

Re: Data validation depending on other data validation without named lists

Re: Data validation depending on other data validation without named lists

Re: Data validation depending on other data validation without named lists

Re: Data validation depending on other data validation without named lists

Re: Data validation depending on other data validation without named lists

Re: Data validation depending on other data validation without named lists

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Can't use Numbers in named range list for Dependent data validation lists

[SOLVED] Data validation: How to create different drop down lists depending on user's response?

Data Validation and Dynamic Named Range Lists

Data Validation and named lists

6 Data Validation lists depending on 1 cell value

Bookmarks

Bookmarks

Posting Permissions