Dropdown list based on other cells vaue

Hi All,

I have a problem which I can't get solved.
Column C and D are should be picked from a dropdown list (data is in named range on sheet 3).
The choices that have t become available in a dropdown list in column E (values also in named rnage on sheet 3) should be based on the combination of column C and D.
The choices that have t become available in a dropdown list in column H (values also in named rnage on sheet 3) should be based on the value of column C.

I'm not sure the way it is set up now (as far as it is setup) is the way to go.
Here is a link to the file, I hope this is clear :S
https://www.dropbox.com/s/1cg78f31zm...opzetje_1.xlsx

Hope someone can help me.

Kind regards.

Noticed the link was wrong.
here is a new link: https://dl.dropboxusercontent.com/u/...opzetje_1.xlsx
Also posted it on here.
opzetje_1.xlsx

try the technique shown in the second part of the video (starts at 4:40):

http://www.youtube.com/watch?v=ff0f_AO3kEA

Thank you haripopuri.

Except for the fact that there's still the same amount of named ranges, it does seem to work:
the choices in E are the ones corresponding to the choice made in column C.
How did you acomplish that?

I also figured out that I misscommunicated something:
The value in column E should be figured out with regard to the choice in column D.
(column E should not be filled in manually, but the value shoul be picked automatically corresponding to the chosen choice in column D).

Ok, I've gotten quite far along but I'm running into one last problem:
the values in column C can have a space (or several spaces). If that is the case, the drop down for column H wil only work on the values without spaces in the name.
I've named the ranges the same as the actual corresponding value but without the spaces but now, working on the INDIRECT forumla, i am stuck. I red something about including SUBSTITUTE in the INDIRECT formula, but I must be doing something wrong because I can't get it to work... :S

I've solved it in a dirty way: the substitute formula is in a hidden row, the data validation then uses that row instead of the original one.
It works but I'd love to know if there is a way of accomplishing the same thing directly.

Just got word that there should also be something about travel allowance... not sure how to build that in yet but I'll keep you posted (i'm afraid)

I had the & at the wrong place, stupid me! sorry!!!
Now I can dive into the travel allowance thing :S (expect another post regarding that)