Referencing Changing File Names

**Dave1674** · 11-15-2007, 07:41 AM

I reference a report which is run every week to make a separate report that uses lots of different formulas and updates automatically. The name of the report that I pull from is constant, but the week's date is different every week. Can I ignore the date only so I can reference this weekly report without changing the name or my references?

For example:

A report is run this week called: Dave's Report 111607.xls
Next wee the report will be called: Dave's Report 112307.xls
And so on...

What I currently do is re-save this report as just Dave's Report.xls in another folder and all my formulas reference Dave's Report.xls.

Can I manipulate each of my formulas to only see the first portion of the file name, so I don't have to re-save the report, I can just open the dated report and let all of my references update?

**Ron Coderre** · 11-15-2007, 08:45 AM

Have you tried....

From the Excel Main Menu:
<edit><links>
....Click [Change Source]
....Browse to the new file
....Click[OK]

Does that help?

**Dave1674** · 11-15-2007, 11:01 AM

Originally Posted by Ron Coderre

Have you tried....

From the Excel Main Menu:
<edit><links>
....Click [Change Source]
....Browse to the new file
....Click[OK]

Does that help?

Thanks. That would be one way. I am really trying to do it in a formula (and not a VBA), so all that has to be done is open the newest "slave" file and the formula file, and the info appears.

Is it possible to have a wildcard in the filename?

**Ron Coderre** · 11-15-2007, 11:28 AM

First, let me say right up front that I'm no fan of linking workbooks. They somehow find a way to become volatile, corrupt, and/or unusable. I pretty much ALWAYS find a better solution than file linking.

That being said....onto your issue:
Any dynamic-formula approach would probably be sluggish to some degree (from somewhat sluggish to very sluggish). The reason is that you'd need to use the INDIRECT function, which is volatile (meaning every INDIRECT function recalculates whenever Excel calculates anything anywhere in the workbook). Another annoyance is that the INDIRECT formulas can't reference the other workbook unless it's open. There are a few add-ins you can download that can reference closed workbooks, as explaind in **** Kuslieka's blog:
http://www.dailydoseofexcel.com/arch...sed-workbooks/
...but all have their respective downside.

IMHO, editing links is the best of the linking options.