Lacan
Board Regular
- Joined
- Oct 5, 2016
- Messages
- 228
- Office Version
- 365
- Platform
- Windows
Hello Guys,
Through your help and many other tutorial excel videos came this way that almost Im achieving the goal - create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row.
Have this massive extent List with 7 columns and more than 2300 rows I will give you the sample data very developed to ask for your expertise feedback.
Without Vba and without filter and sort function (because unfortunately in my professional lap top dont have this 2 functions in excel version).
The Goals:
1. Make data validation with unique values from every List in every Columns (in The Main Table) to make more easier and simple to choose for instead of duplicates ones like this:
2. Create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row:
a) When I choose in The Main Table:
299 Lx - Amadora (MÁgua - Sul) > HOSPITAL JB LONDON > Amadora > (Logical should only get 2 options "Fernando" or "Maria") instead get this:
ALL DATA:
Can you help me guys?
Thank you very much.
Through your help and many other tutorial excel videos came this way that almost Im achieving the goal - create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row.
Have this massive extent List with 7 columns and more than 2300 rows I will give you the sample data very developed to ask for your expertise feedback.
Without Vba and without filter and sort function (because unfortunately in my professional lap top dont have this 2 functions in excel version).
The Goals:
1. Make data validation with unique values from every List in every Columns (in The Main Table) to make more easier and simple to choose for instead of duplicates ones like this:
2. Create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row:
a) When I choose in The Main Table:
299 Lx - Amadora (MÁgua - Sul) > HOSPITAL JB LONDON > Amadora > (Logical should only get 2 options "Fernando" or "Maria") instead get this:
ALL DATA:
Book1 | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | DATA LIST | THE MAIN TABLE | ||||||||||||||
2 | Brick | Center | City | Name | Brick | Center | City | Name | ||||||||
3 | 299 Lx - Amadora (MÁgua - Sul) | CENTRO CLINICO AVENIDA | Amadora | Anna | 299 Lx - Amadora (MÁgua - Sul) | HOSPITAL JB LONDON | Amadora | Dynom | 2 | HOSPITAL JB LONDON | ||||||
4 | 299 Lx - Amadora (MÁgua - Sul) | CENTRO CLINICO AVENIDA | Amadora | Anna Simon | ||||||||||||
5 | 299 Lx - Amadora (MÁgua - Sul) | CENTRO CLINICO AVENIDA | Amadora | Aladin | ||||||||||||
6 | 299 Lx - Amadora (MÁgua - Sul) | CENTRO CLINICO AVENIDA | Porto | Serge | ||||||||||||
7 | 299 Lx - Amadora (MÁgua - Sul) | HOSPITAL JB LONDON | Amadora | Fernando | ||||||||||||
8 | 299 Lx - Amadora (MÁgua - Sul) | CENTRO CLINICO AVENIDA | Amadora | Blyde | ||||||||||||
9 | 299 Lx - Amadora (MÁgua - Sul) | HOSPITAL JACK BARCELONA | Porto | Dynom | ||||||||||||
10 | 299 Lx - Amadora (MÁgua - Sul) | CENTRO CLINICO AVENIDA | Amadora | Venom | ||||||||||||
11 | 300 Lx - Amadora (MÁgua - Sul) | CENTRO CLINICO FRANK | Amadora | Yalan | ||||||||||||
12 | 300 Lx - Amadora (MÁgua - Sul) | Clínica LAR MEDICO | Samouco | Anna | 0 | |||||||||||
13 | 300 Lx - Amadora (MÁgua - Sul) | Clínica LAR MEDICO | Samouco | Costa | ||||||||||||
14 | 300 Lx - Amadora (MÁgua - Sul) | Clínica LAR MEDICO | Samouco | Maria | ||||||||||||
15 | 300 Lx - Amadora (MÁgua - Sul) | Clínica LAR MEDICO | Samouco | Cesar | ||||||||||||
16 | 300 Lx - Amadora (MÁgua - Sul) | Clínica LAR MEDICO | Samouco | Carlos | ||||||||||||
17 | 300 Lx - Amadora (MÁgua - Sul) | HOSPITAL JB LONDON | Amadora | Maria | ||||||||||||
18 | 300 Lx - Amadora (MÁgua - Sul) | Clínica LAR JONH | Amadora | Jesus | ||||||||||||
19 | 301 Lx - Amadora (MÁgua - Sul) | Clínica LAR JONH | Amadora | Maria | ||||||||||||
20 | 301 Lx - Amadora (MÁgua - Sul) | Clínica LAR JONH | Amadora | Luzia | ||||||||||||
21 | 301 Lx - Amadora (MÁgua - Sul) | Clínica MEDICA SA | Lisboa | Guilherme | ||||||||||||
22 | 301 Lx - Amadora (MÁgua - Sul) | Clínica MEDICA SA | Lisboa | Rogerio | ||||||||||||
23 | 301 Lx - Amadora (MÁgua - Sul) | Clínica MEDICA SA | Lisboa | Pedro | ||||||||||||
24 | 301 Lx - Amadora (MÁgua - Sul) | DR PETER SA | Samouco | Albino | ||||||||||||
25 | 302 Lx | CLINIC ABC | Lisboa | Naxos | ||||||||||||
26 | ||||||||||||||||
27 | ||||||||||||||||
Esta (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L3 | L3 | =SUM(COUNTIFS(C3:C25,I3, D3:D25,J3)) |
M3 | M3 | =INDEX(C3:E25, MATCH(I3,C3:C25,0), MATCH(J3,D3:D25,0)) |
H12 | H12 | =COUNTIF(INDIRECT(brick),H12)<2 |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'Esta (2)'!_FilterDatabase | ='Esta (2)'!$B$2:$E$25 | L3:M3, H12 |
brick | ='Esta (2)'!$B$3:$B$25 | H12 |
'Esta (2)'!Lx_299 | ='Esta (2)'!$C$3:$E$10 | L3:M3 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
I4 | List | =INDIRETO(H20) |
I5:I10,J4:K10 | List | =INDIRETO(SUBST(H5;" ";"")) |
H3 | List | =brick |
I3 | List | =DESLOCAMENTO($C$2; CORRESP(H$3;$B$3:$B$25;0);0;CONTAR.SE($B$3:$B$25;$H$3);1) |
J3 | List | =DESLOCAMENTO($D$2; CORRESP(I$3;$C$3:$C$25;0);0;CONTAR.SE($C$3:$C$25;$I$3);1) |
K3 | List | =DESLOCAMENTO($E$2; CORRESP(I$3;$C$3:$C$25;0);0;CONTAR.SE($D$3:$D$25;$J$3);1) |
Can you help me guys?
Thank you very much.