I am developing a summary spreadsheet that will collate information from
several other spreadsheets called data1.xls, data2.xls, data3.xls etc.

I have included links in the summary spreadsheet to the data spreadsheets
using formulas such as:

=IF(ISERROR([data1.xls]sheet1!$A$1),"-",[data1.xls]Sheet1!$A$1)

This (should) check first that data1.xls exists (if not, then creates an
error and "-") is displayed. If data1.xls does exist then cell A1 of sheet 1
is displayed.

I have deliberately not specified the file path as this may change. However
the Summary spreadsheet will always be in the same folder as the data
spreadsheets.

What happens is that when the Summary spreadsheet is opened data is not
always linked and checking the Edit/Links shows the status of each link as
"Unknown".

I can confirm that the Start Up prompt appears and I click "Yes" to update
links.

If you open all the data files separately with the summary spreadsheet
already open then the data updates properly.

It appears that Excel is not looking in the same folder but rather the last
used folder.

How do I get Excel to look in the same folder that the summary sheet is
saved in (without actually naming it and providing a path - the summary
spreadsheet will be a generic one and used by over 200 people with different
sets of data1.xls etc spreadsheets)?

Sorry, it's a bit difficult to explain, but if you need any further info
please let me know.

Thank you in advance for reading/helping.

In cases similar to this, I require my users to input their basic PATH into a
certain cell in a SETUP sheet, also their NAME, etc........then I use this
data in my macros.
It may not be the slickest, but it works for me and the users don't seem to
mind.....like it kind of "customizes" the program for each of them..

Vaya con Dios,
Chuck, CABGx3




"B Baggins" wrote:

I am developing a summary spreadsheet that will collate information from
several other spreadsheets called data1.xls, data2.xls, data3.xls etc.

I have included links in the summary spreadsheet to the data spreadsheets
using formulas such as:

=IF(ISERROR([data1.xls]sheet1!$A$1),"-",[data1.xls]Sheet1!$A$1)

This (should) check first that data1.xls exists (if not, then creates an
error and "-") is displayed. If data1.xls does exist then cell A1 of sheet 1
is displayed.

I have deliberately not specified the file path as this may change. However
the Summary spreadsheet will always be in the same folder as the data
spreadsheets.

What happens is that when the Summary spreadsheet is opened data is not
always linked and checking the Edit/Links shows the status of each link as
"Unknown".

I can confirm that the Start Up prompt appears and I click "Yes" to update
links.

If you open all the data files separately with the summary spreadsheet
already open then the data updates properly.

It appears that Excel is not looking in the same folder but rather the last
used folder.

How do I get Excel to look in the same folder that the summary sheet is
saved in (without actually naming it and providing a path - the summary
spreadsheet will be a generic one and used by over 200 people with different
sets of data1.xls etc spreadsheets)?

Sorry, it's a bit difficult to explain, but if you need any further info
please let me know.

Thank you in advance for reading/helping.