Dynamic validation question

[Moet_Chandon]

Hi all,

I do have a little issue with dynamic validation.

There are 3 tabs which are concerned:
An overview of codes and description (just text)

Excel Workbook
	A	B
1	WBS element	Naam
2	812/0320	SIAM Prg Mgt
3	812/0320	SIAM Prg Mgt
4	812/0320.01	SIAM Prg Mgt Voorbereiding
5	812/0320.01.01	SIAM Prg Mgt Voorbereiding
6	812/0320.01.01.01	SIAM Prg Mgt VB Projectleiding
7	812/0320.01.01.02	SIAM Prg Mgt VB Projectoverleg
8	812/0320.01.01.03	SIAM Prg Mgt VB PSA
9	812/0320.01.01.04	SIAM Prg Mgt VB POC
10	812/0320.01.01.05	SIAM Prg Mgt VB Requirements
11	812/0320.01.01.06	SIAM Prg Mgt VB PB
12	812/0320.01.01.07	SIAM Prg Mgt VB PID
13	812/0320.01.01.08	SIAM Prg Mgt VB Planning
WBS_Totaal

An overview of employees (medewerkers) and codes which the are allowed to use (WBS 1 till WBS 6).

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Naam	Personeelsnr	Uurtarief	Geautoriseerde kostenplaats(en)
2				WBS 1	WBS 2	WBS 3	WBS 4	WBS 5	WBS 6
3	Alexander Grave	464479	100,00	812/0320.01.01.01	812/0320.01.01.03	812/0320.01.01.07
4	Anne Helle Rixen	9675	75,00	812/0324.02.50.03	812/0331.03.01.03	812/0327.03	812/0329.01.01.02
5	Amir Karimi Ghavanlou	114302	112,50	812/0320.02.01.06
6	Arjan Juurlink	6735	88,50	812/0320.02.01.06	812/0320.01.01.02	812/0320.02.01.08
7	Dirk Cikot Roos	470912
8	Edwin Dekker	467439
9	Edwin Ponsioen	175616
Medewerkers_WBS

There is a validation at each cell of the WBS columns. It is defined as List and the source is WBS_CODES. WBS_CODES is a name with te following source:
=OFFSET(WBS_Totaal!$A$2;0;0;COUNTIF(WBS_Totaal!$A$2:$A$1000;"<>");1)

An financial tab with an overview of the employees and actual hours per WBS code.

Excel Workbook
	A	B	C	D	E
1	Naam	Personeelsnr	Week 9 (28 feb - 06 maa)
2			WBS	Uren	Kosten
3	Alexander Grave	464479		10	1.000,00
4	Alexander Grave	464479		3	300,00
5	Alexander Grave	464479		4	400,00
6
7	Totaal			17	1.700,00
8	Anne Helle Rixen	9675		12	900,00
9	Anne Helle Rixen	9675		4	300,00
10
11
12	Totaal			16	1.200,00
Medewerkers - Uren

Cell Formulas
Range	Formula
A4	=IF(AND(D4="",H4="",L4="",P4="",T4="",X4=""),"",A3)
A5	=IF(AND(D5="",H5="",L5="",P5="",T5="",X5=""),"",A4)
A6	=IF(AND(D6="",H6="",L6="",P6="",T6="",X6=""),"",A5)
A9	=IF(AND(D9="",H9="",L9="",P9="",T9="",X9=""),"",A8)
A10	=IF(AND(D10="",H10="",L10="",P10="",T10="",X10=""),"",A9)
A11	=IF(AND(D11="",H11="",L11="",P11="",T11="",X11=""),"",A10)
D7	=SUM(D3:D6)
D12	=SUM(D8:D11)
E3	=IF($A3<>"",VLOOKUP($A3,Medewerkers_WBS!$A$3:$C$300,3,FALSE)*D3,"")
E4	=IF($A4<>"",VLOOKUP($A4,Medewerkers_WBS!$A$3:$C$300,3,FALSE)*D4,"")
E5	=IF($A5<>"",VLOOKUP($A5,Medewerkers_WBS!$A$3:$C$300,3,FALSE)*D5,"")
E6	=IF($A6<>"",VLOOKUP($A6,Medewerkers_WBS!$A$3:$C$300,3,FALSE)*D6,"")
E7	=SUM(E3:E6)
E8	=IF($A8<>"",VLOOKUP($A8,Medewerkers_WBS!$A$3:$C$300,3,FALSE)*D8,"")
E9	=IF($A9<>"",VLOOKUP($A9,Medewerkers_WBS!$A$3:$C$300,3,FALSE)*D9,"")
E10	=IF($A10<>"",VLOOKUP($A10,Medewerkers_WBS!$A$3:$C$300,3,FALSE)*D10,"")
E11	=IF($A11<>"",VLOOKUP($A11,Medewerkers_WBS!$A$3:$C$300,3,FALSE)*D11,"")
E12	=SUM(E8:E11)
B3	=IF(A3<>"",IF(A3<>"Totaal",IF(VLOOKUP($A3,Medewerkers_WBS!$A:$E,2,FALSE)=0,"",VLOOKUP($A3,Medewerkers_WBS!$A:$E,2,FALSE)),""),"")
B4	=IF(A4<>"",IF(A4<>"Totaal",IF(VLOOKUP($A4,Medewerkers_WBS!$A:$E,2,FALSE)=0,"",VLOOKUP($A4,Medewerkers_WBS!$A:$E,2,FALSE)),""),"")
B5	=IF(A5<>"",IF(A5<>"Totaal",IF(VLOOKUP($A5,Medewerkers_WBS!$A:$E,2,FALSE)=0,"",VLOOKUP($A5,Medewerkers_WBS!$A:$E,2,FALSE)),""),"")
B6	=IF(A6<>"",IF(A6<>"Totaal",IF(VLOOKUP($A6,Medewerkers_WBS!$A:$E,2,FALSE)=0,"",VLOOKUP($A6,Medewerkers_WBS!$A:$E,2,FALSE)),""),"")
B7	=IF(A7<>"",IF(A7<>"Totaal",IF(VLOOKUP($A7,Medewerkers_WBS!$A:$E,2,FALSE)=0,"",VLOOKUP($A7,Medewerkers_WBS!$A:$E,2,FALSE)),""),"")
B8	=IF(A8<>"",IF(A8<>"Totaal",IF(VLOOKUP($A8,Medewerkers_WBS!$A:$E,2,FALSE)=0,"",VLOOKUP($A8,Medewerkers_WBS!$A:$E,2,FALSE)),""),"")
B9	=IF(A9<>"",IF(A9<>"Totaal",IF(VLOOKUP($A9,Medewerkers_WBS!$A:$E,2,FALSE)=0,"",VLOOKUP($A9,Medewerkers_WBS!$A:$E,2,FALSE)),""),"")
B10	=IF(A10<>"",IF(A10<>"Totaal",IF(VLOOKUP($A10,Medewerkers_WBS!$A:$E,2,FALSE)=0,"",VLOOKUP($A10,Medewerkers_WBS!$A:$E,2,FALSE)),""),"")
B11	=IF(A11<>"",IF(A11<>"Totaal",IF(VLOOKUP($A11,Medewerkers_WBS!$A:$E,2,FALSE)=0,"",VLOOKUP($A11,Medewerkers_WBS!$A:$E,2,FALSE)),""),"")
B12	=IF(A12<>"",IF(A12<>"Totaal",IF(VLOOKUP($A12,Medewerkers_WBS!$A:$E,2,FALSE)=0,"",VLOOKUP($A12,Medewerkers_WBS!$A:$E,2,FALSE)),""),"")

What I like to have is a validation in column C (where I put the right WBS Code) which depends on the employee from column A, so I only will be able to choose codes which are allocated to this employee (which is done at the tab Medewerkers_WBS).

I will appreciate any help and/or suggestion.

 

[GlennUK]

In the tab Medewerkers_WBS have defined names for the WBS area per employee ( you could have some code in the Worksheet_Change event area to generate the defined names whenever any change is done in that sheet ).

To see what I mean you could generate the names manually for now ... in the tab Medewerkers_WBS define names of:
Alexander_Grave to refer to: =Medewerkers_WBS!$D$3:$F$3
Anne_Helle_Rixen to refer to: =Medewerkers_WBS!$D$4:$G$4
Amir_Karimi_Ghavanlou to refer to: =Medewerkers_WBS!$D$5
Arjan_Juurlink to refer to: =Medewerkers_WBS!$D$6:$F$6

And in sheet Medewerkers - Uren in column C use these names to do the Data Validation, like this ( Data/Validation/List )
=INDIRECT(SUBSTITUTE(A3," ","_"))

 

[Moet_Chandon]

Thanks for your reply. I will take a look at it. It will not before this weekend because of other activities.