Excel - External file linking 1

[Creto]

I'm not sure if this one is even possible, but if it is, it would make things a lot easier.

I have a template that I use weekly and it summarizes category data from other spreadsheets during that week's time. Since the template is doing a week at a time, I have columns for the dates that increment based on the first date entered. The files that it references are directly tied to the dates, ie, 20060515. Under each date is summary information being pulled from the other spreadsheets, so in the case of 20060515, it is pulling from 20060515.xls within the same directory. An example part of one of the summary formulas looks like this:

IF([20060515.xls]TRACKING!$B$3="Org",[20060515.xls]TRACKING!$C$3,0)

I am wanting this to be dynamically based off the date so it will automatically know which files to look in because this is a template, so idealy, it would be something along the lines of (presuming B2 had the value: 20060515):

IF('[' & B2 & '.xls]TRACKING!$B$3="Org"',[20060515.xls]TRACKING!$C$3,0)

As I said, I'm not even sure if this is possible, but if so, it would alleviate a lot of modifications per template use.

 

[xlbo]

Have a look at the INDIRECT function. Caveat - won;t update on closed files

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Creto]

Just realized I only half wrote the last line how i wanted it... idealy it would be something like this...

IF('[' & B2 & '.xls]TRACKING!$B$3="Org"','[' & B2 & '.xls]TRACKING!$C$3',0)

 

[Creto]

xlbo,

Thanks for the reply. Perhaps I have the syntax wrong, but I do not see how the Indirect function will perform what I am looking for.

If there is a file named 20060515.xls and there is a cell, B5, that has the value 20060515, how can I make B6 reference the 20060515.xls file *using* the B5 value?

(hardcoded version)
=[20060515.xls]TRACKING!$B$3

(what i'm looking for... well, with a corrected formula of course)
='[' & B5 & '.xls]TRACKING!$B$3'

 

[xlbo]

did you bother to read the help file for INDIRECT ????

=INDIRECT("[" & B5 & ".xls]TRACKING!$B$3")


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Creto]

xlbo,

In regards to your first comment, I direct you to my first comment... "Thanks for the reply. Perhaps I have the syntax wrong..." which is exactly what the case was. I was incorrectly using the INDIRECT function *after* I had read the help file.

Anyways, thanks for clearing up how I was incorrectly using that function. That works exactly as I wanted.