Contents
Creating a simple drop-down list in a cell with data from the same file is an easy task. Things get more complicated when the data for the list is in another file. To solve this problem, you can use the function INDIRECT (INDIRECT)to form the correct link to the external file.
For example, if you need to create a drop-down list with the contents of cells A1: A10 from the sheet List from file Products.xls, you need to open the data verification window through the menu or tab Data – Validation (Data — Validation) data and in the field Source (Source) enter the following structure:
=INDIRECT(“[Products.xls]List!$A$1:$A$10“)
The INDIRECT function converts the argument’s text string to a real address used to refer to the data. Note that the file name is enclosed in square brackets, and the exclamation mark is used to separate the sheet name from the cell range address. If the file name contains spaces, then it must be enclosed in apostrophes.
If the file with the source data for the list is located in another folder, you must specify the full path to the file, for example, as follows:
=INDIRECT(“‘C:TEMP[Products.xls]List‘!$A$1:$A$10“)
In this case, don’t forget to enclose the full path to the file and the sheet name in apostrophes.
PS
There is only one minus of this whole system – the drop-down list will work correctly only if the file Products.xls open 🙁
- Create drop down list in worksheet cells
- Creating Dependent Dropdowns
- Auto Generate Dropdown Lists with PLEX Add-In Tools
- Selecting a photo from the dropdown list
- Dropdown list with automatic deletion of already used items
- Dropdown list with automatic addition of missing items