Using Cell Reference for external data look up

[matt121376]

I am working on a spreadsheet for calculating energy savings. The biggest factor is weather data. I want to use the same worksheet regardless of where the project is located and I want to use weather data from a library of other excel files. I want to create a pull down menu with the list of cities that I have weather data for. Then i want to import the weather data for the city that is selected from the pull down menu but I can't figure out how to do it. Anyone have any ideas? I tried using the pull down menu to return the file name for that city and using that in an embedded formula but it doesn't work.

[Chance2]

Matt,

A couple assumptions here, you can write the formula to access the data now you just need it to be dynamic, right?

If so, assuming your "city" is linked in cell A1, then you could use INDIRECT to get to the file.

Assumption #1 - you have a formula something like this to get the data...

Please Login or Register  to view this content.

Where [Workbook] is the value you need dynamic?

You could use:

Please Login or Register  to view this content.

Where A1 is the city.

[matt121376]

That's basically what I'm trying to do except that when I use the formula you gave me, I get #REF! in the cell. Here are 2 of my files. The predictor.xls is the energy savings calculator and MA-Boston.xls is my weather data. I'm not really sure what I'm doing wrong.

[matt121376]

I was reading the help section for the INDIRECT function and it noted that the external reference file must be open. Is there a way to do this with all external files remaining closed? Ideally, I'd like to lock all the weather data files so that they cannot be changed.

[Chance2]

Matt,

Sorry I missed that about indirect.

INDEX allows you to see into closed files, but it does not allow for variable file names.

I am starting to think this may require some VBA coding to populate the data after the text box is selected. Something like this might work (I'm just not sure where to put it)...

Please Login or Register  to view this content.