Need Excel Template Manipulated for Data Entry By Others

I need to make sure I have controls in place for an Excel Template that will be used for gathering data. What I want is: 1. I want the macros to automatically apply and be running once the file opens – force the macros to apply upon opening 2. I want the drop down data selection for High Level function to automatically apply the applicable data selections in the drop down for Function1 Column (see references tab) 3. If I pick a Function Role in column V, the High Level Function 1 and Function1 in columns W and X should automatically be populated with the mapping in the references tab. Also, the High Level Function 2 and Function 2 should be cleared out. 4. If Functional Role is empty, the drop downs should be available for columns W and X on High Level Function and Function. People should be able to select high level function and function, even if there is no functional role. However, if there is a selection for a cell in column V made, any data in the corresponding cells for columns W and X, high level function and function, are cleared, and filled in with instead the exact values (possibly on v lookup) of the appropriate high level function and function from the mapping in the reference tab.