Let's say I have a spreadsheet with two worksheets = SheetA and SheetB.

SheetA might reference a cell in SheetB with a formula like =SheetB!A1

But when I move this to the network the reference changes to include the
network drive and file name like:

='Z:\FOLDER\[FILE]SheetB!A1

the file may move from my laptop to the network several times and this
becomes completely confusion as the reference looks, not within the same
spreadsheet which is what I want it to to, but for another file out on the
network.

How do I explicitly reference a cell within a difference worksheet but
always within the same spreadsheet?

The issue came about when the spreadsheet on one drive found an old copy on
the network and pulled bad data.

Thanks
4Nails

Hi 4Nails,

Let's say I have a spreadsheet with two worksheets = SheetA and SheetB.

SheetA might reference a cell in SheetB with a formula like =SheetB!A1

But when I move this to the network the reference changes to include the
network drive and file name like:

='Z:\FOLDER\[FILE]SheetB!A1

This is NOT normal behaviour of Excel. You should go into Edit, links then
click change source and point to THE FILE YOU HAVE OPEN. That should fix
this permanently. If not, I suspect your file may be corrupt.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

You can protect you your references by putting them in a string:

=INDIRECT("SheetB!A1")

--
Gary''s Student - gsnu200724


"4Nails" wrote:

Let's say I have a spreadsheet with two worksheets = SheetA and SheetB.

SheetA might reference a cell in SheetB with a formula like =SheetB!A1

But when I move this to the network the reference changes to include the
network drive and file name like:

='Z:\FOLDER\[FILE]SheetB!A1

the file may move from my laptop to the network several times and this
becomes completely confusion as the reference looks, not within the same
spreadsheet which is what I want it to to, but for another file out on the
network.

How do I explicitly reference a cell within a difference worksheet but
always within the same spreadsheet?

The issue came about when the spreadsheet on one drive found an old copy on
the network and pulled bad data.

Thanks
4Nails

Actually this happens to all of my files I move back and forth from the
network and has caused a considerable amount of error. I have gone in and
edited the references but they keep popping up.

4Nails

"Jan Karel Pieterse" wrote:

Hi 4Nails,

Let's say I have a spreadsheet with two worksheets = SheetA and SheetB.

SheetA might reference a cell in SheetB with a formula like =SheetB!A1

But when I move this to the network the reference changes to include the
network drive and file name like:

='Z:\FOLDER\[FILE]SheetB!A1

This is NOT normal behaviour of Excel. You should go into Edit, links then
click change source and point to THE FILE YOU HAVE OPEN. That should fix
this permanently. If not, I suspect your file may be corrupt.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

That may work. Thanks

"Gary''s Student" wrote:

You can protect you your references by putting them in a string:

=INDIRECT("SheetB!A1")

--
Gary''s Student - gsnu200724


"4Nails" wrote:

Let's say I have a spreadsheet with two worksheets = SheetA and SheetB.

SheetA might reference a cell in SheetB with a formula like =SheetB!A1

But when I move this to the network the reference changes to include the
network drive and file name like:

='Z:\FOLDER\[FILE]SheetB!A1

the file may move from my laptop to the network several times and this
becomes completely confusion as the reference looks, not within the same
spreadsheet which is what I want it to to, but for another file out on the
network.

How do I explicitly reference a cell within a difference worksheet but
always within the same spreadsheet?

The issue came about when the spreadsheet on one drive found an old copy on
the network and pulled bad data.

Thanks
4Nails

Give it a try and see if it works for you.


A similar technique can be used to protect hyperlink references. Enclose
the UNCs in doublequotes and use the =HYPERLINK() function
--
Gary''s Student - gsnu200724


"4Nails" wrote:

That may work. Thanks

"Gary''s Student" wrote:

You can protect you your references by putting them in a string:

=INDIRECT("SheetB!A1")

--
Gary''s Student - gsnu200724


"4Nails" wrote:

Let's say I have a spreadsheet with two worksheets = SheetA and SheetB.

SheetA might reference a cell in SheetB with a formula like =SheetB!A1

But when I move this to the network the reference changes to include the
network drive and file name like:

='Z:\FOLDER\[FILE]SheetB!A1

the file may move from my laptop to the network several times and this
becomes completely confusion as the reference looks, not within the same
spreadsheet which is what I want it to to, but for another file out on the
network.

How do I explicitly reference a cell within a difference worksheet but
always within the same spreadsheet?

The issue came about when the spreadsheet on one drive found an old copy on
the network and pulled bad data.

Thanks
4Nails

Hi 4Nails,

That may work. Thanks

"Gary''s Student" wrote:

You can protect you your references by putting them in a string:

While it may work, your Excel somehow does things it should NOT do.
This is abnormal behaviour.

I'd suggest to reinstall Office first before doing all sorts of work
arounds.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com