About "Day" problem + & sign for string concatenation

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


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