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?

Sub pathaa()
MsgBox ActiveWorkbook.Path

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dable" wrote in message
...
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?

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?

Don,

My sincere thanks for your very quick reply, but I must confess my
ignorance. Is your answer intended to be used to find, or place the current
path in the summary spreadsheet cells, or neither?

"Don Guillett" wrote:

Sub pathaa()
MsgBox ActiveWorkbook.Path

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dable" wrote in message
...
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?

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?

Short answer: It doesn't matter what you put.

Explanation: The CELL function is asking for details about the reference
cell, not the contents. As it's asking for the filename, it doesn't even
really matter what cell you reference, as long as its a cell from the
workbook! Thus, the absolute reference really isn't necessary, and I
apologize for the confusion it caused.

Also, since it's asking for details, and not contents, there is no need to
place anything into cell A1, but if you want to/need to, feel free to go
ahead, it will have no effect on the function.

Word form explanation of formula:
CELL functions finds filepath name of cell (aka, the workbook), giving
something like "C:/My Documents/[Book1.xls]Sheet!A1". Find function then
removes specific workbook details, giving "C:/My Documents/". The INDIRECT
function then combines this with the text string of your other workbook name
w/ cell refence giving "C:/My Documents/[Workbook you want.xls]Sheet1!A1".
The function then looks in this destination, returns value.

All functions used are fairly well explained in the Help section of XL, if
you want futher info.
--
Best Regards,

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


"Dable" wrote:

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?