Indirect cell references ????

Giganews Newsgroups
Subject: Indirect cell references ????
Posted by:  Stephen Rainey (stephenrain…@hotmail.com)
Date: Tue, 2 May 2006

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.

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

Replies