Excel 2007 : Automatic weekly update

[imcloood]

Hi,

I receive a weekly spreadsheet with new data, that i need to put into a different spreadsheet.

I would like to know how to make it so i can just automatically update the data in my own spreadsheet by simply inputting the date.

For example, i get the weekly spreadsheet on 11/05/2010. The name of the file is SS11052010. The data i need to copy is in cell A2. Normally i would open both spreadsheets, and into my spreadsheet do, =[SS11052010.xlsm]$A$2. There are many other cells that need to be copied over, that are the same cell every week.

Next week i will get the file called SS111210.xlsm, with new data to be copied over.

Is there any way i can simply enter the date of the new file, and have my spreadsheet automatically pull the data i need?

Thanks.

[ConneXionLost]

Are you already using Excel's Replace feature (Ctrl-h):

Find "SS11052010.xlsm" and Replace (with "SS111210.xlsm")?

Cheers,

[imcloood]

Upon further inspection, the weekly files have some random differences besides just the date...ugh that complicates things and makes it so i cannot simply do ctrl-h.

Also, to complicate things even more, the cell is not always the same from week to week. For example, last week one of the data was in cell D23, the next D22, and another of the data was in column F, and now is in column E.

Shoot this is going to be impossible.

[imcloood]

ok. here is the next thing i guess i could try. The numbers i want to return are always next to the same words.

Example

Gas-100
Oil-200
Sugar-50

Can i do a lookup formula to return the number next to the word, where all i would have to do was update the file name?

so something like lookup gas in file x

then to update, just copy and paste and replace file x with file y?