Combo Box with Input Range that refers to a range in another file
Combo Box with Input Range that refers to a range in another file
(OP)
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.
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.





RE: Combo Box with Input Range that refers to a range in another file
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
RE: Combo Box with Input Range that refers to a range in another file
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.