Pulling data from separate workbook?

[rawkitz]

Hi,

First post! I was hoping someone could point me to a macro or method to pull data from a data file (Excel file) into my workbook. Catch is that the source workbook constantly changes (new one is made for each instance with updated data). The worksheet name within each source workbook is the same, however. It would be nice if Excel would prompt me for which file I wanted to use, at which point it would populate the cells in interest. The source workbook won't be in the same directory as my working workbook, which is why it would be nice if I could tell it which file to pull data from.

So an example:
Open Workbook1. Workbook1 immediately references Worksheet3 in Workbook2 for data to pull into Worksheet5 in Workbook1. (Got all of that?) From there Workbook1 then does calculations based on the data pulled form Workbook2, Worksheet 3.

I know this is a confusing description, but I'd think this has been done before. Help?

[patel45]

can you attach a sample with befor and after situation ?

[rawkitz]

I can't attach data due to copyright/proprietary reasons. Is something unclear that I can help you understand? I do want to make it clear, however, that the data file I'm going to be pulling data from will be changing, so a prompt asking for a file path would be really useful.

Thanks.