Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
princess_21 Profile
 | Femeie
19 ani
Bucuresti
cauta Barbat
31 - 44 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 18 ani
Postari: 2241
|
|
Text below is copy / paste from Source website at the bottom of this post.
Source also has pictures. Added some here also.
****
Functions used: OFFSET, MATCH, COUNTA, COUNTIF, INDIRECT
Step 1: Prepare the Source Tables
Our first step is to create the source tables that we will use for the contents of the drop-down lists.
Make a first table with 1 column of categories (parent list) and then a second table (child list) that includes 2 columns, the first with the categories repeating themselves for each child in the 2nd column.
VERY IMPORTANT: Child list must be sorted by category, so that all the category items are grouped together in the list (see image above, column D).
Step 2: Create the Parent Drop-down List
Dependent drop-down lists are not a built-in feature of Excel. Therefore, we need to get creative with some functions and formulas to create the dynamic dependencies between the lists.
Create a data validation in one cell, choose List and under Source put the following formula:
=OFFSET(Sheet3!B1; 1; 0; COUNTA(Sheet3!B:B)-1; 1)
Or if you are using Tables you could also use the following formula:
=INDIRECT("PARENT_TABLE_NAME[COLUMN_NAME]")
eg.
=INDIRECT("Table1[Category]")
Step 3: Create the Child (Dependent) Drop-down List
We now need to create the child list, which is dependent on the selection of the parent list.
Either use the formula with ranges:
=OFFSET(Lists!$D$1; MATCH(B6; Lists!$D:$D; 0)-1; 1; COUNTIF(Lists!$D:$D; B6); 1)
or the formula for tables:
=OFFSET(INDIRECT("CHILD_TABLE_NAME[#Headers]"); MATCH(B6; INDIRECT("CHILD_TABLE_NAME[Category]"); 0); 1; COUNTIF(INDIRECT("CHILD_TABLE_NAME[Category]"); B6); 1)
****
Source:
_______________________________________
|
|
| pus acum 2 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
corrected error in last formula: there was B11, changed it to B6
|
|
| pus acum 1 an |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
Adding a 3rd drop down list dependent each (except for the first, of course) on the previous:
My data is structured as follows:
1. Table1 contains 1 column
- named Company
- contains company names
2. Table2 contains 2 columns
- the first one is named just like the column in Table1 - Company
- the second column is named Location
- the second column contains several locations per company
- all rows are completed and each company is repeated in the first column for each location in the second column
3. Table3 contains 3 columns
- the first one is named just like the column in Table1 - Company
- the second column is named just like the second column in Table2 - Location
- the third column is named Building
- the third column contains several buildings per location per company
- all rows are completed and each company and location is repeated in the first and second columns respectively for each building in the third column
I inserted drop down lists using Data Validation - List
1st drop down list is located in cell B6 and contains the following formula:
=INDIRECT("Table1[Company]")
2nd drop down list is located in cell B7 and contains the following formula:
=OFFSET(INDIRECT("Table2[#Headers]"); MATCH(B6; INDIRECT("Table2[Company]"); 0); 1; COUNTIF(INDIRECT("Table2[Company]"); B6); 1)
3rd drop down list is located in cell B8 and contains the following formula:
=OFFSET(INDIRECT("Table3[#Headers]"); MATCH(1; (INDIRECT("Table3[Company]")=B6) * (INDIRECT("Table3[Location]")=B7); 0); 2; COUNTIFS(INDIRECT("Table3[Company]"); B6; INDIRECT("Table3[Location]"); B7); 1)
|
|
| pus acum 5 luni |
|
Mrrrr
AdMiN
Inregistrat: acum 18 ani
Postari: 2241
|
|
The above method apparently creates some issues on the 3rd drop down list, as Excel has issues doing OFFSET and INDEX and INDIRECT within data validation, apparently.
Symptoms are:
- upon selecting the first 2 drop down lists, everything was OK
- when clicking on the arrow of the 3rd drop down list, nothing shows, as if there is no list
- which 3rd drop down list cell selected, going to data validation, opening it and simply clicking OK without any change, allows the 3rd drop down list to show as intended
In the description from the post above there is no mention of the location of the 3 drop down lists, so here they are:
- 1st drop down list = Company = Sheet1!D8
- 2nd drop down list = Company = Sheet1!D9
- 3rd drop down list = Company = Sheet1!D10
Solution (pretty long, but it works):
TESTED: Office 365, Office 2019
1. In Excel, press CTRL+F3 to open Name Manager (or go to Formulas - Name Manager), and now we will create several Named ranges by clicking the New button
First named range (for 1st drop down list)
- under Name (mandatory) add a representative name for your 1st drop down list - I named it ndl_COMPANY
- under Scope, Workbook is selected by default and should remain selected
- provide a Comment (optional) if you want to explain what the formula will do
- under Refers to, add a formula similar to the following formula, corresponding to the cell where your 1st drop down list is located (including the sheet reference):
Second named range (for 2nd drop down list)
- similarly to the steps above for the 2nd drop down list - I named it ndl_LOCATION
Third named range (for 2nd Excel Table)
- similarly to the above add a Name and Comment - I named it nLOCATION
- under Refers to, add a formula similar to the following:
| =INDEX(Table2[Location]; MATCH(ndl_COMPANY; Table2[Company]; 0)):INDEX(Table2[Location]; MATCH(ndl_COMPANY; Table2[Company]; 1)) |
This essentially creates a range of all the values in Table2[Location] corresponding to the Company selected in your 1st drop down list.
Fourth named range (for 3rd Excel Table)
- similarly to the above add a Name and Comment - I named it nBUILDING
- under Refers to, add a formula similar to the following:
| =INDEX(Table3[Building]; MATCH(ndl_COMPANY & ndl_LOCATION; INDEX(Table3[Company] & Table3[Location];); 0)):INDEX(Table3[Building]; MATCH(ndl_COMPANY & ndl_LOCATION; INDEX(Table3[Company] & Table3[Location];); 1)) |
This essentially creates a range of all the values in Table2[Building] corresponding to the Company selected in your 1st drop down list and the Location you selected in the 2nd drop down list.
You can now Close Name Manager.
2. Click on your 2nd drop down list cell and go to Data - Data Validation, and under Source put the following formula and confirm with OK: =nLOCATION
- confirm even if you receive an error saying "The Source currently evaluates to an error. Do you want to continue?"
- the error above appears because there is no company selected in the first drop down list (to confirm that this is the case if you don't believe me, click Cancel, select a company in the 1st drop down list and after try entering the above formula in Data Validation - no error will pop up now)
3. Click on your 3rd drop down list cell and go to Data - Data Validation, and under Source put the following formula and confirm with OK: =nBUILDING
- confirm even if you receive an error saying "The Source currently evaluates to an error. Do you want to continue?"
4. Go the your Excel Tables and sort them as follows:
- Table2, by the 2nd column A-Z, and afterwards by the 1st column A-Z
- Table3, by the 2nd column A-Z, and afterwards by the 1st column A-Z - you can also do it starting with the 3rd column, then 2nd, then 1st, but I encountered no problem if the 3rd column was not sorted
Source:
ChatGPT (most of the solution)
_______________________________________
|
|
| pus acum 5 luni |
|
