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" <ardus.petus@laposte.net> wrote in message
news:emO3H6gbGHA.3320@TK2MSFTNGP04.phx.gbl...
> =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" <ardus.petus@laposte.net> a écrit dans le message de news:
> %23hY2fwebGHA.3800@TK2MSFTNGP04.phx.gbl...
>> About "Day" problem + & sign for string concatenation
>>
>> "=[parts" & FORMAT(MONTH($A21),"00")
>> &"]widgets"&FORMAT(DAY($A21),"00")&"$B$99")
>>
>> HTH
>> --
>> AP
>>
>> "Stephen Rainey" <stephenrainey@hotmail.com> a écrit dans le message de
>> news: uVKIHlebGHA.3956@TK2MSFTNGP05.phx.gbl...
>>> 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
>>>
>>
>>
>
>