Data Validation with multiple dependencies

[BWellman]

I am looking to make a few drop down lists that are dependent on each other. The first cell (C3) is a standard data validation with only two options "Operational-Area" or "Corporate". For cell C3, I made a "Data Management" tab and put the list of two, which I referenced as a "list" in data validation.

I am having a problem with two subsequent data validation cells, both dependent on cell C3. In cell C4, I am looking to have a drop down list that only drops down if "Operational-Area" was selected in C3. There are a list of 8 "Operational-Areas", which I made in the "Data Management" tab, but I run into two problems. To use the "Indirect" data validation function, I have to be able to name the list something with only one word, and "Operational-Area" doesn't count, so I was wondering what other alternatives are. The second problem is that I would like cell C4 to be greyed out, or say "N/A" if "Corporate" was chosen in C3.
I would like to do the same thing in cell C5 with "Corporate", but I don't have the first problem as it is only one word, and I just use "Indirect(C3)" in the source input.

I have attached an example excel file of the problem for reference.

ExcelHelp5.xlsx

[p24leclerc]

Look at the attached workbook build with a macro made by MickG. You can certainly find his original post.
You put your list of Validation variables in up to 4 columns in "Sheet1"
In "Sheet2", the macro will automatically create the 4 data validation cells.
You can play with the code to place those cells where you want.
Thanks to MickG
Hope this helps