Excel VBA Customised Validation List

[TykiMikk]

I'm trying to create a customised dropdownlist with Validation and I'm theorising and want to know if this is possible

In the red highlight box FLEALLSTAFF I'm wanting to combine all the values where Region = FLE or Region = ALL


I'm trying to do this by using a For Loop and comparing each cell to see if Region = FLE or Region = ALL. If it does then add it to the NameRange (NameRangeOfFLEAKLStaff).


If that's possible then in the source of my dropdown list I want to use the following code:


IF((AND(C5 = "BEC",C5 = "FEC"),StaffAKLCHC,=OFFSET(STAFF!$A$1,MATCH($C$5,STAFF!$A:$A,0)-1,1,COUNTIF(STAFF!$A:$A,$C$5),1)


The code above will check if C5 is BEC or FEC, If it's true then it will use the source from the named range StaffAKLCHC else it will use my formula which just finds the source from the staff tab.


C5 is a cell that changes depending on what I select in the region drop down list


IF(B2="FLE",B2,(IF(B2="BEC",B2,"ALL")))


meaning if the selection in the region drop down list is FLE or BEC then C5 will be FLE or BEC if not then it will be ALL


Here is the link for the dummy workbook: https://mega.nz/#!y8NSEA7T!2OvEQxvMDLZMp4u5FB5gZ6xhP-xs3ZP47gYMipcUZcM