Data Validation

[thishan]

HI All,

I am wanting to use Data Validation to give me a drop-down list on Spreadsheet A from Spreadsheet B.

Both Spreadsheets are saved onto the same server, and I have access to both however when trying to select the source of information for the data validation, Excel is not recognising the Spreadsheet B selection. Both sheets are worked on via Teams/ Browser FYI, Any support would be appreciated.

[#2]

@thishan 

From all indications, you're trying to do something not supported by Data Validation--use a list in another spreadsheet/workbook as the source. 

That said, if it is essential that the list in B be the source for the data validation in A, I was able to use FILTER in A to retrieve a list dynamically from B (dynamically meaning it would grow as the original in B grew).

The formula in cell E3 of spreadsheet A is

=FILTER([Book4]Sheet1!$E$5:$E$10,ISTEXT(LEFT([Book4]Sheet1!$E$5:$E$10,1)))

and the data validation dialog box looks like this