Input Range

  • Thread starter Thread starter PeterM
  • Start date Start date
P

PeterM

I'm using Excel2003. I have a combobox in file "A" and need to assign the
input range to an range of cells in file "B". If I have file "B" open the
input range for the combobox in file "A" works great, however, whenever I
close file "B", the input range is no longer valid. The input range in file
"B" is used in several workbooks and I need to reference it in all of these
workbooks. I don't want to have to copy the input range from File "B" to all
of these other workbooks as the data changes frequently.

Is there a way to set the input range for a combobox in one file to a range
of cells in another file while the other file is not opened?

Thanks in advance for your help!
 
You can use an embedded query to the other spreadsheet (Data, Import External Data, Import Data, then select the file and named range). Make sure your range has a header row or you will lose the first row.
This will work fine but will lock the other spreadsheet when in use because it is really opening the other spreadsheet behind the scenes.
 
I know it is a hassle doing this but you should only need to do it once.
Identify an area in file A that is not in use or even insert another
worksheet for the purpose.

Copy the range in file B and paste special -> link into File A.

Then set the input range (rowsource) of the combo to the range in file A. It
does not matter that file B is not open because the links will update anyway
and the combo should have the correct data.

You said that the data in file B changes frequently but you should only have
to set the links up once. Then when file B is changed it should change in all
the other workbooks. The main criteria is that file B must be in the same
location all the time or the other workbooks will not be able to find it to
update the links.
 
Back
Top