How to create DropDown which have dynamic Validation List

0 votes

In my data table, the columns "Category" and "Product" are present. Product names and Category names are both common, yet the pair Category-Product only appears once in the database. There is no sorting at all in this dataset.

Category  Product

========  =======

Chairs      Victorian

Chairs      Beautiful

Chairs      Edwardian

Chairs      Gross

Tables      Victorian

Tables      Edwardian

Tables      Huge

Tables      Kool

Tables      Lambda

Closets     Edwardian

Closets     Excellent

Closets     Major

Closets     Hello

Chairs      Huge

Tables      Picturesque

Closets     Picturesque

Chairs      Incredible

Closets     Minor

Chairs      Just

Chairs      Kool

I have already created temporary table with unique Category names, which will be used as validation range for the first dropdown list. This part works as it should.

Categories

==========

Chairs

Tables

Closets

The second dropdown should dynamically generate a list of the products under the category you choose in the first dropdown, which is next to the first.

I can use temporary columns to produce a result that is sufficient if there are only one set of dropdowns (Category/Product).

Category:   Chairs           Product: Victorian

                                                  Beautiful

                                                  Edwardian

                                                  Gross

                                                  Huge

                                                  . . .

The problem is that these dropdown pairs should be part of another table with columns: 'Category', 'Product', and 'Amount'. So, when the user chooses in the first column the Category dropdown value 'Chairs', in the next column dropdown should be available in the list of just Products from the category 'Chairs'. In the next row when the user chooses the category 'Tables', the adjacent cell should be available just products from the category 'Tables'.

I am trying to make this using just formulas, array formulas, and named functions (do not mix up them with UDF functions) without VBA code.

Is it possible to do it or I am wasting my time (2 days already)?

Oct 31 in Others by Kithuzzz
• 20,660 points
37 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Others

0 votes
1 answer

How to create Dropdown list in excel using php

Try this: $objValidation = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getDataValidation(); $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST ); $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
129 views
0 votes
1 answer

How to create a drop-down list in Excel?

Making a list of the items you ...READ MORE

answered Oct 9 in Others by narikkadan
• 37,660 points
52 views
0 votes
1 answer

How to create a dependent drop down list using [Apache POI]

There is nothing apache poi cannot do, ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
153 views
0 votes
0 answers

Simple way to remove blank cells dynamic dropdown list Excel

Every time I create a dependent dynamic ...READ MORE

Nov 2 in Others by Kithuzzz
• 20,660 points
41 views
0 votes
0 answers

how to list the contents of a asset into an event

May 29, 2019 in Others by anonymous
247 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18 in Others by Edureka
• 13,640 points
74 views
0 votes
1 answer
0 votes
1 answer
0 votes
0 answers

Excel formula converting tan to angle

I have an excel spreadsheet that computes ...READ MORE

Oct 31 in Others by Kithuzzz
• 20,660 points
25 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP