Dynamic validation question

Moet_Chandon

New Member
Joined
Mar 20, 2011
Messages
2
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
AB
1WBS elementNaam
2812/0320SIAM Prg Mgt
3812/0320SIAM Prg Mgt
4812/0320.01SIAM Prg Mgt Voorbereiding
5812/0320.01.01SIAM Prg Mgt Voorbereiding
6812/0320.01.01.01SIAM Prg Mgt VB Projectleiding
7812/0320.01.01.02SIAM Prg Mgt VB Projectoverleg
8812/0320.01.01.03SIAM Prg Mgt VB PSA
9812/0320.01.01.04SIAM Prg Mgt VB POC
10812/0320.01.01.05SIAM Prg Mgt VB Requirements
11812/0320.01.01.06SIAM Prg Mgt VB PB
12812/0320.01.01.07SIAM Prg Mgt VB PID
13812/0320.01.01.08SIAM 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
ABCDEFGHI
1NaamPersoneelsnrUurtariefGeautoriseerde kostenplaats(en)
2WBS 1WBS 2WBS 3WBS 4WBS 5WBS 6
3Alexander Grave464479 100,00812/0320.01.01.01812/0320.01.01.03812/0320.01.01.07
4Anne Helle Rixen9675 75,00812/0324.02.50.03812/0331.03.01.03812/0327.03812/0329.01.01.02
5Amir Karimi Ghavanlou114302 112,50812/0320.02.01.06
6Arjan Juurlink6735 88,50812/0320.02.01.06812/0320.01.01.02812/0320.02.01.08
7Dirk Cikot Roos470912
8Edwin Dekker467439
9Edwin Ponsioen175616
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
ABCDE
1NaamPersoneelsnrWeek 9 (28 feb - 06 maa)
2WBSUrenKosten
3Alexander Grave46447910 1.000,00
4Alexander Grave4644793 300,00
5Alexander Grave4644794 400,00
6
7Totaal17 1.700,00
8Anne Helle Rixen967512 900,00
9Anne Helle Rixen96754 300,00
10
11
12Totaal16 1.200,00
Medewerkers - Uren
Cell Formulas
RangeFormula
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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,545
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

New Member
Joined
Mar 20, 2011
Messages
2
Thanks for your reply. I will take a look at it. It will not before this weekend because of other activities.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,510
Messages
5,602,075
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top