INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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.

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

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

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close