Cell Reference Problem with Network

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
Click to expand...

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
 
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 said:
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
Click to expand...

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
Click to expand...
 
That may work. Thanks

Gary''s Student said:
You can protect you your references by putting them in a string:

=INDIRECT("SheetB!A1")

--
Gary''s Student - gsnu200724


4Nails said:
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
Click to expand...
Click to expand...
 
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 said:
That may work. Thanks

Gary''s Student said:
You can protect you your references by putting them in a string:

=INDIRECT("SheetB!A1")

--
Gary''s Student - gsnu200724


4Nails said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top