This is a difficult one.....I have posted this before and what you see below is as far as it has gotten. Please help!

I am writing a formula in cell "B1" in "December 2007 REVISED BY DAN.xlsm" that will reference cell "X1" in a workbook that has not been created yet.
The way I will tell the formula what the name of the workbook will be is by referencing cell "A1" and adding the extension ".xlsx" to it.

Example:
"December 2007 REVISED BY DAN.xlsm"
_A________|B______________________________________ __________________________________________________ __________________________________|C
1_DANTEST_|__=INDIRECT("C:\Documents and Settings\DBryant\desktop\B.D.I. SPREADSHEET PROJECT\CONTINUATION SHEETS\'["&A1&".xlsx]Sheet1'!X1")___________|__
2_________|_______________________________________ __________________________________________________ ________________________________|__

It doesn't work.
I receive a reference error. I would expect this to happen if the contents of "A1".xlsx did not yet exist, but infact it does.
The value of "A1" in my workbook containing the formula is "DANTEST"
The workbook C:\Documents and Settings\DBryant\desktop\B.D.I. SPREADSHEET PROJECT\CONTINUATION SHEETS\DANTEST.xlsx
does currently exist, and cell "X1" has the contents "HOORAY!". (a little celebration if I can get the formula to work

What next?
-Any help is appreciated

Dingo0Z wrote:
INDIRECT("C:\Documents and
Settings\DBryant\desktop\B.D.I. SPREADSHEET PROJECT\CONTINUATION
SHEETS\'["&A1&".xlsx]Sheet1'!X1")


I think your first ' is in the wrong place.
Should be after the first "

INDIRECT("'C:\Documents and
Settings\DBryant\desktop\B.D.I. SPREADSHEET PROJECT\CONTINUATION
SHEETS\["&A1&".xlsx]Sheet1'!X1")

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Dingo0Z wrote:
NDIRECT("C:\Documents and
Settings\DBryant\desktop\B.D.I. SPREADSHEET PROJECT\CONTINUATION
SHEETS\'["&A1&".xlsx]Sheet1'!X1")


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup