Major help needed
 

Hi,

I have been working with excel for about 3 years now and I've never seen this.

I have sheets that have index formulas that search other folders, stored in
different locations for reference values. The formula has the valid
reference and address for the folder to look in.

Somehow if I save the results folder to a different location it changes the
address in which to look for the database file - without any interaction
whatsoever from me!

I have been using the same computer/formats etc for ages. Nothing has
changed. It started on one file and now I've seen it happen on another one.

Any idea how this can happen?

Lindsey

Major help needed
 

XL is somewhat lazy when creating outside references. If the workbook you are
referencing is currently in the same folder, XL only stores "in the folder
I'm currently in, find this file". This of course causes problems if you move
the original file, as the referenced files did not move along with it.

My recommendation would be to make sure XL is storing the full path name of
your referenced workbooks before moving the file to a new location.
--
Best Regards,

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


"LiAD" wrote:

Hi,

I have been working with excel for about 3 years now and I've never seen this.

I have sheets that have index formulas that search other folders, stored in
different locations for reference values. The formula has the valid
reference and address for the folder to look in.

Somehow if I save the results folder to a different location it changes the
address in which to look for the database file - without any interaction
whatsoever from me!

I have been using the same computer/formats etc for ages. Nothing has
changed. It started on one file and now I've seen it happen on another one.

Any idea how this can happen?

Lindsey

Major help needed
 

Hi,

Thanks for your help.

As far as i know thats what i am doing. For example my 'proper'
address/formula is;

=IF($D7="";"";INDEX('J:\Planification secteur Precision\UP1\Production
journaliere\Production journaliere\[Base de
données.xls]Données'!$J$5:$J$25;MATCH($D7;'J:\Planification secteur
Precision\UP1\Production journaliere\Production journaliere\[Base de
données.xls]Données'!$I$5:$I$25;0)))

When i copy it to a new location such as my desktop it changes this to

=IF($D7="";"";INDEX('C:\Documents and Settings\name\Desktop\[Base de
données.xls]Données'!$J$5:$J$25;MATCH($D7;'C:\Documents and
Settings\name\Desktop\[Base de données.xls]Données'!$I$5:$I$25;0)))

Is this not the full address?

If i do a copy paste on any of the cells that need the reference it asks me
to refind the folder (i.e. remap the address).

Thanks

"Luke M" wrote:

XL is somewhat lazy when creating outside references. If the workbook you are
referencing is currently in the same folder, XL only stores "in the folder
I'm currently in, find this file". This of course causes problems if you move
the original file, as the referenced files did not move along with it.

My recommendation would be to make sure XL is storing the full path name of
your referenced workbooks before moving the file to a new location.
--
Best Regards,

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


"LiAD" wrote:

Hi,

I have been working with excel for about 3 years now and I've never seen this.

I have sheets that have index formulas that search other folders, stored in
different locations for reference values. The formula has the valid
reference and address for the folder to look in.

Somehow if I save the results folder to a different location it changes the
address in which to look for the database file - without any interaction
whatsoever from me!

I have been using the same computer/formats etc for ages. Nothing has
changed. It started on one file and now I've seen it happen on another one.

Any idea how this can happen?

Lindsey