Combo Box with Input Range that refers to a range in another file

[SteveGregory]

I have a series of combo boxes (form control) that each reference a range of cells in another spreadsheet for the Input Range. These other spreadsheets each contain a large database that I prefer to keep in separate files.

The problem comes when I open the workbook with the combo boxes. The combo boxes do not have any data to drop down unless I also load the other workbook files with the data. I am using Excel 2007. With a formula, I can reference a range of cells in another worksheet that is not open. For example. I can use the Index function to reference a range in another workbook file, but it doesn't work with a combo box.

I prefer using the 97-2003 .xls file format if possible. I tried saving the file to the .xlsx format to no avail.

Is there an Excel Option that I can select to make this work so that i don't have to open the database spreadsheet files?

The only other option might be some type of a macro that runs as soon as the workbook opens to also open these two other database xls files. I am not sure how to set up this type of autorun macro. Autorun macros are a little scary to me. Perhaps a better alternative would be a macro assigned to a button to open these files.

 

[swertel]

I would create another tab (sheet) and "import" the range of data from the external spreadsheet using a reference link.
Then, set up your control box to use the local range as input for the list of values.
Hide the tab.

--Scott
www.wertel.pro

 

[SteveGregory]

Thanks, Scott
If I understand what you are suggesting, I would create a column of formulas to serve as the input for each combo box. The formulas will link the data labels from the external "data" spreadsheets.

I tried it out and it worked. That was too easy! Thanks again.