Jun 27 2022 11:22 AM
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.
Jun 27 2022 11:49 AM - edited Jun 27 2022 11:51 AM
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