Hi AP, thanks for your help with this..
I have just had a breakthrough/down and have it working using the following
=INDIRECT("'C:\PATH\[Daily
Log-"&TEXT(F9,"yyyymmdd")&".xls]Activities'!$F$2").
I simplified the concatenation to get the basic functionality working,
and I think that I can now adopt this to my original problem.

BTW: if =FORMAT(DAY(F20),"00") is put into a cell, the
following error is generated. #NAME?

Do I need to install something? What am I missing ?



"Ardus Petus" wrote in message
...
=INDIRECT(CONCATENATE("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99")
&"]widgets"&FORMAT(DAY($A21),"00")&"$B$99"))

Not tested, but should work...

Cheers,
--
AP

"Ardus Petus" a écrit dans le message de news:
...
About "Day" problem + & sign for string concatenation

"=[parts" & FORMAT(MONTH($A21),"00")
&"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")

HTH
--
AP

"Stephen Rainey" a écrit dans le message de
news: ...
Hi Folks,
I have looked far and wide for an answer to this issue,
and I am hoping that someone here can help.

I create "summary" sheets of workbooks so that important information
can be seen at a glance on one page.

In order to simplify the preparation of summary sheets, I want to be
able to use the date value in column A
to derive the name of the books and sheets referenced in adjacent cells.

When attempting to concatenate text and date functions, the formula do
not resolve, not sure what I am doing wrong.

I hope the following illustration conveys my meaning.

Any help will be appreciated.

A B C D
Date Orders Received Fuel Used Widgets
Shipped

01 24-Dec =[book12]sheet24$A$1 =[car12]sheet24$A$26
=[parts12]widgets24$B$99
............................
12 02-Aug =[book08]sheet02$A$1 =[car08]sheet02$A$26
=[parts08]widgets03$B$99
13 01-Aug =[book08]sheet01$A$1 =[car08]sheet01$A$26
=[parts08]widgets03$B$99
14 31-Jul =[book07]sheet31$A$1
=[car07]sheet31$A$26 =[parts07]widgets31$B$99
........................
21 15-May =[book05]sheet15$A%1 =[book05]sheet15$A$26
=[parts05]widgets15$B$99

My attempt to put this in a formula is as follows. However it does not
resolve as is desired.




("=[parts",MONTH($A21),"]widgets",DAY($A21),"$B$99") ------------^

P.S. I am also have trouble getting the date functions to resolve to 2
digit day and month values.

Steve Rainey