Link to external file for dropdown list

Hey Gang:

I’ll try again.

FILE A contains some formatted data. FILE B contains a list of cities.

I need to reference the cities in FILE B in order to populate a cell in FILE A.

How do I reference/link the list in FILE B???

Thanks, Rich Ramik

It is my understanding that you want to link to data stored inside an external secondary spreadsheet, is this correct?


FILE A will have the link to the drop down list that is stored in FILE B (city names).

Ideally, the list in FILE B will be seen in FILE A, then select the appropriate city.

What’s the problem?
Open both files. Select file B. Select all cities and copy. Switch to file A, in an empty space, press Ctrl+Shift+V, mark Link and press OK.


Press F2 to go to cell editing, select the resulting formula and copy. Create a data validity allow cell range, in the Source paste the formula you just copied. Make sure everything works as it should and clear the inserted list of cities, it is no longer needed.

I know what you are talking about but this does not solve the problem.

The “calling” workbook is actually a purchase order. I need to reference a list of cities in workbook B.

I followed your instructions, but ended up with an error reference. See the following.


Rich Ramik

Don’t think the #REF! Is correct.

You’re right, after this step, everything else no longer made sense. In cells B3: B5 City 1 .City 2. City 3 should have appeared, and not an error. Hmmm… Can you try doing it on some other computer? Perhaps there is a problem with the settings of this particular computer (system, office)?

Will do later today. I’ll post results.

By the way, I’m running

If you wish to link to data stored in an external file then try following these instructions:
Link to External Data

This is inserting all of the cities in FILE B. I need a drop down of the cities so I am able to select the appropriate city.