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:
crazylife 25 ani
 | Femeie
25 ani
Bucuresti
cauta Barbat
25 - 47 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
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: 739
|
|
corrected error in last formula: there was B11, changed it to B6
|
|
| pus acum 8 luni |
|
