Cell Reference Problem with Network

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
0
4Nails (3)
5/25/2007 2:42:01 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
288 Views

Similar Articles

[PageSpeed] 14

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

0
jkpieterse (271)
5/25/2007 4:10:42 PM
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
0
GarysStudent (1572)
5/25/2007 4:28:00 PM
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
> 
> 
0
4Nails (3)
5/25/2007 4:51:02 PM
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
0
4Nails (3)
5/25/2007 4:55:00 PM
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
0
GarysStudent (1572)
5/25/2007 5:07:01 PM
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

0
jkpieterse (271)
5/26/2007 10:04:53 AM
Reply:

Similar Artilces:

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

Problem with CFTPConnection
Hi I've an application that connects to an FTP Server and uploads a big directory (size over 500 MB). Now what happens is that after sometime the application stops uploading files and stays idle. The FTP server connection timeout value is set to 2 minutes. So the server seeing the connection idle for two seconds, disconnect the session and no more file is uploaded. On debugging I found out that after some time CFTPConnection.PutFile() method hangs on some random file and stays like that for 5 minutes (default send timeout value of CFTPSession). During this time the server connection timeo...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

Errors saving to network drive
When my users open an Excel spreadsheet located on a network drive, make their changes, and then try to save the spreadsheet, they get an error message saying "Your changes could not be saved to Whateverfile.xls, but were saved to XXXXXXXX ( 8 digit hex number ). Close the existing document, then open the temporary document and save it under a new name" The users have been explicitly granted full rights, including Delete and Modify, to the shared folder in which the spreadsheet resides. I have disabled their Symantec Antivirus realtime protection for the time being. This pr...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

CRM Outlook Client Configuration Wizard Problem
CRM 4.0 - Microsoft Outlook Client Guys, We've Microsoft CRM 4.0 Server roll up 8 as well as CRM Outlook Client on the same roll up version. Our Internal Web Access works without problems users get to it and they're automatically authenticated. IFD Development is the same way except they're prompt through a login page for their username and password when they're coming from an external network. Ever since we moved to a new facility users are experiencing problems connecting to CRM using their outlook client, I have a test machine where I have the CRM Outlook Client ins...

Reference Book
I'm a Win32 programmer, and I'm trying to get into some MFC. I've read a little about MFC, but the books lack a lot of quality and descriptive text about the structure of MFC. Can I ask whose book is the Charles Petzold of MFC? Thank you I haven't seen a bad MFC book in a long time, but then again, I haven't bought a new MFC book in many years. My presonal perference are the MFC books published by Wrox Press http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&isbn=1861000855&itm=31 You can't go wrong with the Microsoft Press ones either. Al...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Missing Reference
Argh! There was a missing reference, " Microsft DAO2.5/3.5 Compact ..." I unselected it and backed out - now, every date field is filled with today's date and several other fields have gone to the 'dark side'. The $ field shows $0.00 for everyone and another field is blank. I went back into the dialog box and I can't find that reference in the list to reselect it. Thank goodness I experimented using a copy, eh? >-----Original Message----- >Your references are probably messed up. >If any of the selected references have "MISSING:" in front ...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Sorting Cells by Colors
Hi all, Is it possible to write a VBA code to sort excel cells by colors, and the followed by other criterias, as in the normal sort? Thank you in advance. Hi SwiftCode, See Chip Pearson's Sorting By Color page at: http://www.cpearson.com/excel/SortByColor.htm --- Regards, Norman "swiftcode" <swiftcode@discussions.microsoft.com> wrote in message news:FC1550A7-A8DD-4EC0-B171-F1DB4373C35C@microsoft.com... > Hi all, > > Is it possible to write a VBA code to sort excel cells by colors, and the > followed by other criterias, as in the normal sort?...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...