University performance - Excel Tool

 Absolutely! Here’s a step-by-step guide to create an Excel data entry form with dependent dropdowns for stakeholder feedback — all on a single sheet.

---

Step 1: Prepare Your Data Lists

1. Open a new Excel workbook.

2. Create a new sheet called Lists (you can hide it later).

3. In Lists, enter the Affiliations vertically starting from cell A2, e.g.:

A
Affiliation (header)
Students
Staff
Industry
Society

4. Next to it, create columns for each affiliation with their respective Parameters, starting from row 2. For example:

A	B	C	D	E
Affiliation	Students	Staff	Industry	Society
	Quality of Teaching	Work Environment	Graduate Employability	Social Responsibility
	Curriculum Relevance	Workload & Job Satisfaction	Curriculum Relevance	Inclusivity
...	...	...	...	...

(Add all parameters under each affiliation column)

---

Step 2: Define Named Ranges

1. Select the list of Affiliations (e.g., Lists!$A$2:$A$5), then go to Formulas > Define Name, name it Affiliations.

2. Similarly, for each affiliation’s parameter list (e.g., Lists!$B$2:$B$11 for Students), create named ranges exactly matching the affiliation name (e.g., Students, Staff, Industry, Society).

---

Step 3: Setup the Data Entry Sheet

1. Go back to your main worksheet, e.g., name it Feedback Form.

2. In row 1, create headers:

A	B	C	D	E	F
Respondent Name	Affiliation	Date	Parameter	Rating	Comments/Suggestions

3. Format the headers as bold and widen columns for clarity.

---

Step 4: Add Data Validation for Affiliation

1. Select the cells in the Affiliation column where users will input data (e.g., B2:B100).

2. Go to Data > Data Validation.

3. Under Allow, select List.

4. In Source, enter: =Affiliations

5. Click OK.

---

Step 5: Add Dependent Dropdown for Parameters

1. Select the cells under Parameter column where users will input data (e.g., D2:D100).

2. Go to Data > Data Validation.

3. Under Allow, select List.

4. In Source, enter the formula: =INDIRECT(B2)

   • This formula makes the Parameter list depend on the value selected in Affiliation column in the same row.

5. Click OK.

Note: For this to work properly, the affiliation names must exactly match the named ranges created in Step 2.

---

Step 6: Add Data Validation for Rating

1. Select the Rating column cells (e.g., E2:E100).

2. Go to Data > Data Validation.

3. Under Allow, select List.

4. Enter the source as: 1,2,3,4,5

5. Click OK.

---

Step 7: Add Date Picker (Optional)

• Excel doesn’t have a native date picker in all versions, but you can format the Date column (C2:C100) as Date:

  • Select the cells.

  • Right-click > Format Cells > Date > Choose desired format.

---

Step 8: Final Touches

• Freeze the header row (View > Freeze Panes > Freeze Top Row).

• Optionally, hide the Lists sheet to prevent accidental edits.

• Add instructions at the top or a separate sheet for users.

• Protect the sheet to prevent editing headers or lists if desired.

---

If you want, I can also provide you with sample formulas or a downloadable step-by-step checklist.

Would you like me to prepare any additional tips or example screenshots?