Excel: Dynamic file refrence

[Funkymatt]

)

I've got several files all with the same name in a directory (ModelCompMMDDYYYY.xls). The current day file has a need to pull some historical values.

I have a cell, D5 that contains the historical date (MM/DD/YYYY) for the data I am interested in finding. I'd like to pull the date and use it in my cell refrence when I'm pulling historical data.

This is what I was thinking but it's not working:

='[ModelComp&MONTH(D5) & DAY(D5) & YEAR(D5).xls]TBA - Reg Settle'!$N$8

Help appreciated,
Matt

 

[SkipVought]

Hi,

You're shooting yourself in the foot having your similar data chopped up into a multitude of separate files/sheets -- whatever. ALL your similar data ought to reside in a single table.

If I were you, I'd spend my effort gathering the data together.

Then, the kind of reporting question you are posing could be accomplished in mere SECONDS.

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[AnotherHiggins]

I wholeheartedly agree with Skip. You should have all of the data together.

But to answer your question directly - have a look at the indirect function.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[Funkymatt]

Skip,
I would do that but...
When the market closes each day the LIBOR and market curve data needs to be saved in a file for that particular day. A market dash board if you will.

The sheet that I'm looking into now pulls one or two numbers from a prior day's close, a full month ago market close, and a year ago market close to indicate the LIBOR and overall market trends.

Thanks for your advice though but this is the way it's got to stay...

 

[SkipVought]

Really? Why does it have to be that way? Just because it the way its always been done???

You call yourself a PROGRAMMER. Well, act like one rather than following some arbitrary decision.

Each day when the market closes, process that data into a table. If someone needs separate workbooks, hey, its there for them! What you need to do a programmer's job, is a consolidated table.

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]