Linking to Workbooks that Change Name Daily

[pwk]

I am trying to link to another workbook to extract data daily. The
problem is the page name changes on a daily basis. Can I use wildcards

to do this? Maybe there's another way. Here are the references:

'[DOC - Daily Composite.xls]142007, 1'!$F$381


'[DOC - Daily Composite.xls]742996, 1'!$F$381


Thanks!

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

I am trying to link to another workbook to extract data daily. The
problem is the page name changes on a daily basis. Can I use wildcards

to do this? Maybe there's another way. Here are the references:

'[DOC - Daily Composite.xls]142007, 1'!$F$381

'[DOC - Daily Composite.xls]742996, 1'!$F$381

Thanks!

 

[pwk]

Could you please give me an example of the formula. The formula I'm
using the first day is:

='[DOC - Daily Composite.xls]142007, 1'!$E$381&"/"&'[DOC - Daily
Composite.xls]142007, 1'!$F$381 Giving me a result of E381/F381

If the page name changes the next day to: DOC - Daily
Composite.xls]2142007, 1

How can I access it without writing a new formula. Using wild-cards
like this doesn't work:

DOC - Daily Composite.xls]*2007, 1

Any Help would be appreciated

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

I am trying to link to another workbook to extract data daily. The
problem is the page name changes on a daily basis. Can I use wildcards

to do this? Maybe there's another way. Here are the references:

'[DOC - Daily Composite.xls]142007, 1'!$F$381

'[DOC - Daily Composite.xls]742996, 1'!$F$381

Thanks!

 

[undrline via OfficeKB.com]

Is there a pattern to the change? 142007, 2142007, 4142007, 6142007 or
somesuch?
If not, do all the formulae access just the one file, or are there multiple
files?

I believe Excel takes file names as a string, so you could probably open the
file, tell Excel not to update, change the name of "setting cell" (A1, in
this example), then update the linkage:
"[DOC - Daily Composite.xls]"&A1&"2007"

Could you please give me an example of the formula. The formula I'm
using the first day is:

='[DOC - Daily Composite.xls]142007, 1'!$E$381&"/"&'[DOC - Daily
Composite.xls]142007, 1'!$F$381 Giving me a result of E381/F381

If the page name changes the next day to: DOC - Daily
Composite.xls]2142007, 1

How can I access it without writing a new formula. Using wild-cards
like this doesn't work:

DOC - Daily Composite.xls]*2007, 1

Any Help would be appreciated

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

[quoted text clipped - 14 lines]