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, 2022 in Others by Kithuzzz
• 38,010 points
458 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, 2022 in Others by narikkadan
• 63,700 points
1,885 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, 2022 in Others by narikkadan
• 63,700 points
516 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, 2022 in Others by narikkadan
• 63,700 points
4,938 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, 2022 in Others by Kithuzzz
• 38,010 points
979 views
0 votes
1 answer

How to remove values from drop-down if value deleted from validation list

Try this code: Private Sub Worksheet_Change(ByVal Target As ...READ MORE

answered Dec 11, 2022 in Others by narikkadan
• 63,700 points
411 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

Feb 18, 2022 in Others by Edureka
• 13,670 points
278 views
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,700 points
1,439 views
0 votes
1 answer

Excel function for divide or split number to maximum possible equal parts

The underlying math for this is as ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,700 points
2,083 views
0 votes
0 answers

Excel formula converting tan to angle

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

Oct 31, 2022 in Others by Kithuzzz
• 38,010 points
249 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