Create a drop down list in Excel on Sharepoint

I can't reproduce that. For example, in Excel for web I stay on Sheet4 and added values to data validation list from Sheet5

Or what do you mean by "Excel on Sharepoint" ?

Try to fix by the following:

1. Use a Named Range

• Go to the sheet with your list (in the desktop app).
• Select the 5 cells you want to use.
• Go to Formulas → Define Name → Give it a name like DropdownOptions.
• Then go to your dropdown cell → Data Validation → List → Type:

=DropdownOptions

• Save and upload the file back to SharePoint.

2. Move the List to the Same Sheet

• Copy the 5 options to a hidden column on the same sheet.
• Use that range directly in your data validation.

3. Use Excel Desktop to Set It Up

• Open the file in Excel Desktop.
• Set up the dropdown using the full feature set.
• Save and sync it back to SharePoint.
• Excel Online will usually respect the validation once it’s already in place.

ASourKraut​  Please mark this as resolved if it resolves your issue.

In Excel for the Web (SharePoint/OneDrive), data validation list ranges must be on the same sheet. The web version does not allow referencing ranges on other tabs — it just throws an error.

Solution

• Keep the list on the same sheet
• Move (or copy) your 5 rows of dropdown values to the same sheet where you want the dropdown.

You can hide these rows/columns later to keep things clean.