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.
>
>
> CATENATE("=[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
>