Excel 2007 : V/HLOOKUP - automatically reference a new workbook?

[KFye]

I am trying to use H/VLOOKUP to consolidate data for import into our financials DB. I'm using the following:

=VLOOKUP(I1, '[user@ourcompany-inc.com_ZSummary_09152010.xls]Loc. COMPANY-38038'!$B$10:$G$121,5,)

The file is received daily, and it has multiple tabs per day. The only part of the file name that changes daily is the date. When I perform a find/replace to update the date to the next day, I have to point about 100 cells individually to the new workbook. Is there any way to automatically update the workbook reference without having to manually redirect each cell?

I'm proficient in Excel but only recently have started exploring the more in-depth factors of the program, so talk down to me please.

Thanks,
Kristal

[TMS]

Can you do a global replace? Replace yesterday's date with today's date.

Or, you could perhaps use INDIRECT in your VLOOKUP and reference a single cell with the date in it. Only one thing to change. :-)

Regards

[KFye]

The global replace causes Excel to open up a Browse box for every cell that changes (a lot of cells). I'm reading up on indirect, and I will let you know if i find any success! Thanks!

[TMS]

Are you selecting the "Replace All" option?

Regards

[KFye]

Yes, and the Find file browser window opens up 116 times, so I sit and hit Escape for a while. lol

[TMS]

Apologies. I misunderstood when you said "Browse box"

Regards

[KFye]

My lingo isn't technical to any serious degree. I find that "wonky" is an adequate description for most errors. Anyway, I expanded the file name to include the actual file path, and that enabled me to perform a global Find/Replace without asking me to browse for the file each time. Your INDIRECT is going to be very helpful though! I am going to incorporate that and see if I can make a one cell date reference that auto updates all the cells. Thanks for your help!

[TMS]

You're welcome. You've done most of the work.