Excel 2007 : Change workbook filename in formula.

[Taste2Bad]

Hi.
I'm wondering if there is an easy solution to the following "problem":
Every day I produce about 5-10 new workbooks containing analysis results. Each file is named the same as the analysis number, like X00101.xlsx, x00102.xlsx, etc. I also have one workbook which is a logbook of all analysis I have done, and it contains data from each analysis file. In column A of the logbook I have the analysis number (X00101, etc) and in column B I have the formula =[X00101.xlsx]Sheet1!$B$2. And now to the "problem": Every time I save a new analysis file and I put in the analysis number in column A of the logbook, I would have to manually edit the formula in column B to match the new analysis filename. Is there an easy way to make the formula change automatically? Like if I type X00104 in A6, the formula in B6 should automatically be =[X00104.xlsx]Ark1!$B$2.

Thanks.

[sweep]

Hi,

Like this?

="["&A6&".xlsx]Ark1!$B$2"

[Taste2Bad]

Hi,

Like this?

="["&A6&".xlsx]Ark1!$B$2"

Thank you for you reply.
Your solution will only display the formula as a text in the cell and not execute it.

[sweep]

I guess by execute it you mean return the contents of the cell?

=INDIRECT("["&A6&".xlsx]Ark1!$B$2")

This will only work if the destination workbook is open. To obtain values from closed workbooks, please do a quick search of the forum, it's a topic of frequent discussion.

[Taste2Bad]

I guess by execute it you mean return the contents of the cell?

Yes, you guessed correct.

=INDIRECT("["&A6&".xlsx]Ark1!$B$2")

This will only work if the destination workbook is open. To obtain values from closed workbooks, please do a quick search of the forum, it's a topic of frequent discussion.

It worked excellent.
Thank you, this was a big help!
Merry Xmas!