Luke,

Many thanks. I believe this is just what the doctor ordered. I am unclear,
however, about the ("filename",A1) reference and the ("filename",$A$1)
absolute reference and what goes there. Is the INDIRECT function
extrapolating the path from the A1 cell, and do I have to place the text
"summary report.xls" in cell A1? I apolgize for not being up on this, as I
feel I need to be.

Best regards,
Dable

"Luke M" wrote:

I believe so. You have different directories containing workbooks, and you
want to move the "summary" workbook around?

First, for this to work, you will need to save (at least temporarily) the
summary workbook to the appropriate directory. We can then combine the CELL
function with the INDIRECT function.

Original formula:
='H:\Data\Test Scripts\Tests\version 4_04\[TEST160 -
Reports.xls]Reports'!$D$32

New formula:
=INDIRECT(LEFT(CELL("filename",A1),FIND("[",CELL("filename",$A$1))-1)&"[TEST160 - Reports.xls]Reports'!$D$32")

Function extracts the directory path of file, and then concatenates it with
your workbook/worksheet/cell address to form a complete reference.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dable" wrote:

I have about 20 spreadsheets containing tests and at the top of all of the
spreadshets is a summary section that has data in cells that is referenced in
a summary spreadsheet. We would like to be able to have the summary
spreadsheet be "portable" in that the reference directory path need not be
the same. We are trying to see if it is possible to place the summary
spreadsheet in different directories containing the 20 test spreadsheets and
have the summary "automatically" pick up the files in the current directory.

The only way we can get it to work now is by either manually changing all of
the referenced directories paths in the summary spreadsheet cells, or by
resetting all of the test spreadsheets back to their original states.

The line below is one of the summary spreadsheet cells and the path to the
directory containing the referenced spreadsheet, (TEST160 - Reports.xls).

='H:\Data\Test Scripts\Tests\version 4_04\[TEST160 -
Reports.xls]Reports'!$D$32

Is there a way to have the summary spreadsheet recognize the current
directory it is in and use that reference to get the data from the other test
spreadsheets in the same directory?