Invalid Cell Reference error

[Ravi]

Hi All,

I am facing one problem, appreciate someone could solve this.

I created 2 different workbook .One is destination and other is source. Now
I have written formula to get values from source workbook. It works fine in
my local system. but when I copy paste these workbook in some other location
so my formula does not work bcoz cell could not get the path of source
workbook since location is changed.

This sounds very weird that every time I have to manually edit the links. I
tried using edit link property and tried to change source location. But cell
get error #refe 'invalid cell reference'.

Is there anyway to define generic path in formula so that wherever I place
my workbook and formula would pick right path automatically.

 

[Pete_UK]

Why not combine the workbooks so that you have a source sheet and a
destination sheet - no problem with external references then.

Hope this helps.

Pete

 

[Ravi]

Hi Pete,

Thanks for your respons,actually i have more than 10 workbook and each
workbooks contains 6-7 worksheet and have logical reasoning not to combine
them.I really need solution so that i can put my template at any place
without failing in function.

Ravi

 

[Spiky]

I don't think there is a practical way to do that. You can get create
dynamic paths with INDIRECT functions, but you'd have to put that in
every formula that links to the other file. And, INDIRECT does not
calculate unless the other file is actually open. And, I think it
would be just as hard to set up and maintain, anyway.

One way to avoid manually changing is to use Replace to change every
single formula at once. This should be about as fast as using the
Links:Change Source commands. But you would need to make sure the path
is perfect to replace it. Copy/Paste would be useful, you can Paste
into the Replace window. Also, don't have the source file open when
you do this because Excel will hide the full path if it is open. And
you need to work on the full path.