Problem using "Indirect"

[VinceJ]

My problem is in understanding the uses of " and ' in the
following formulas.

My files use a person's last name as the file name. In
developing a report one item of data is found in Smith's
record when the record IS NOT on the desktop by this
formula:
='C:\Documents and Settings\All Users\Documents\Daily 2004
\[Smith.xls]Buyer Progress Record'!$B$6

If the record IS on the desktop I can find one item of
data with the following formula (if "Smith" is in A1):
=INDIRECT("'["&A1&".xls]Buyer Progress Record'!$B$6")

How do I modify the formula to fine one item of data if
the file IS NOT on the desktop? Or, is there a better,
more flexible way to accomplish the same task?

 

[Don Guillett]

Indirect does NOT work with closed workbooks.