You can construct references completely from cell text. For example in Book2:

in A1 put Book1
in A2 put accounts
in A3 put B9

The formula:
=INDIRECT("[" & A1 & ".xls]" & A2 & "!" & A3)
will give the same result as the forrmula:
=[Book1.xls]accounts!$B$9

--
Gary's Student


"Abdul" wrote:

Hello,

I have several worksheets in my workbook(1)

and in workbook (2) all the sheet names of workbook(1) in Cells A1 thru
X1

can i use the text in cell A1 thru X1 in a formula to reference the
workbook(1)

for example in cell A2 instead of ='[Workbook 1.xls]Sheet1l'!$A$2 can
i reference Cell A1 in workbook (2) instead of sheet1, since in
workbook (2) cell A1 contains text "sheet1 "

I am looking for a straight formula rather than VBA