I'm trying to create drop down lists in an excel spreadsheet and data validation step is not being accepted. Any troubleshooting advice?

Question

Have followed the steps outlined in Help but result is:

Answer 1

Dear @Siobhan Hannan, 

Thank you for posting your question in the Microsoft Q&A forum.  

Is it correct that the Source Sheet is Location, the sheet containing the actual list of options: "Online", "In person", "External site visit" (Range C2:C4)? And the Destination Sheet (Sheet1), is the sheet you are currently working on and where you want to create the drop-down cell in Column C? 

Could you try Defining a Name for the range C2:C4 on the Location sheet, and then use that name =EventTypes instead of=Location!C2:C4 in the Source field of Data Validation? 

If this is the case, the steps would be: 

Define Name: Click on the sheet tab that contains your list (which appears to be the Location sheet). Select the cells containing the list (C2:C4). On the Formulas tab > Define Name > Define Name. Delete the current cell address and type a simple name without spaces or accents, for example: EventTypes. 

Use in Data Validation: In the Data Validation dialog, in the Source field, type the defined name you created in Step 1, preceded by an equals sign =EventTypes. 

If there’s anything I may have misunderstood, I would truly appreciate it if you could provide a bit more detail to help me better understand your needs.

Looking forward to your response! 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.