Thanks Very much "Ardus Petus" for resolving the leading 0 issue. That is a
neat and simple solution, I like it.

Do you have any idea how i can get the main formula to resolve ?

I originally looked at the INDIRECT function, but I cannot see how it asists
in this situation.

"Ardus Petus" <ardus.petus@laposte.net> wrote in message
news:%23hY2fwebGHA.3800@TK2MSFTNGP04.phx.gbl...
> About "Day" problem + & sign for string concatenation
>
> =CONCATENATE("=[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.
>>
>>
>>
>> NATE("=[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
>>
>
>