+ Reply to Thread
Results 1 to 20 of 20

Excel spontaneously changes formula linking two workbooks

  1. #1
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Excel spontaneously changes formula linking two workbooks

    I have a cell in a worksheet which is linked to a cell in another workbook. In theory the link should update when I open the first workshheet, however the linking formula has changed itself to c:\Users\Michael\AppData\Roaming|Microsoft\Excel\file name and cell reference. It is supposed to link directly to a cell in a workbook in my D: drive and ,because it doesn't, the result in the cell is outdated. I have manually changed the formula to directly link to the appropriate cell but Excel simply changes it back. Help!

    Disclosure: I have posted this query in Mr. Excel but have not received any replies.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    16,218

    Re: Excel spontaneously changes formula linking two workbooks

    Quote Originally Posted by cullism View Post
    Disclosure: I have posted this query in Mr. Excel but have not received any replies.
    Our rules require that you post the actual link to your Mr. Excel post. Mr. Excel has the same rule. Please do so before we continue. Thanks,
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    The link to Mr.Excel is https://www.mrexcel.com/forum/excel-...-workbook.html

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,635

    Re: Excel spontaneously changes formula linking two workbooks

    So what should the link be?

    This suggests to me that you are accessing the file from different locations over a network environment - is this so?

    PS Is your user profile up-to-date?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    Yes, my profile is up to date.

    Both workbooks are in the same folder on the same drive on the same computer. There are two links, actually, both with the same problem. The links should be:

    D:\[DISCOGS.XLSM]SOLD!'$N$1 and

    D:\'[EBAY SALES.XLSM]TOTAL'!$C$1

    I have just re-entered the formulas and they have not yet changed. This happened the last time I corrected the link(s). The change to roaming didn't happen straight away. I will see if it has occurred tomorrow morning.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,635

    Re: Excel spontaneously changes formula linking two workbooks

    Is this one accurate?

    D:\[DISCOGS.XLSM]SOLD!'$N$1

    There seems to be a misplaced apostrophe.

    Is the D drive a hard drive partition, or a USB drive, or something else?

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,635

    Re: Excel spontaneously changes formula linking two workbooks

    Having searched a little around this issue, I think your syntax might be off.

    Can you try these?

    'D:\[DISCOGS.XLSM]SOLD'!$N$1

    and

    'D:\[EBAY SALES.XLSM]TOTAL'!$C$1

  8. #8
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    Thanks AliG,

    I had typed in my formulas so the error in syntax was merely a typing mistake. I have redone my linking formulas, changing the cells to which they were linked and they have not revised themselves to the mysterious Roaming folder. Fingers crossed!

  9. #9
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    The same problem has recurred. The linking formula, which should refer dirtectly to a cell in my D: drive, once again shows c:\Users\Michael\AppData\Roaming|Microsoft\Excel\file name. Why does it refer to a cell in a workbook in my C: and what does roaming refer to?

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,635

    Re: Excel spontaneously changes formula linking two workbooks

    So you are still using Excel 2007? I'm not sure you are ...

    This suggests that you are accessing your Windows profile on multiple machines: is this the case?

  11. #11
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    Yes, I'm still using Excel 2007 and I am using onlt the one computer.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,635

    Re: Excel spontaneously changes formula linking two workbooks

    And what exactly is the D drive? Is it a drive partition? A second internal hard drive? An external hard drive or NAS drive? A USB stick drive? A (home) networked drive?

  13. #13
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    If I remember correctly, it is a drive parition. It was created to be my data drive (i.e. it contains all my images, spreadsheets, etc.).

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,635

    Re: Excel spontaneously changes formula linking two workbooks

    Well, we need to know because it may be relevant. Please find out.

    Can you upload a workbook containing links to ithe D drive for us to look at?

  15. #15
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    Device Manager shows D: drive as a primary partition. Does this help? I have attached the workbook containing the errant formulas (indicated by the arrows).
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    I gather that nobody has an explanation for this phenomenon. My latest idea is to change the linking formulas back to what they are supposed to be (see earlier posts) then password protect the worksheet. Maybe this will stop Excel from changing the formulas.

  17. #17
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    This didn't work. I guess nobody has any ideas.

  18. #18
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,346

    Re: Excel spontaneously changes formula linking two workbooks

    I don't know exactly how Excel/Windows work together on linked workbooks like this. The only time I have ever had Excel change a link like you describe is when Excel crashes and the file reopens from autorecover. But that is the only time I see the phenomenon you describe is related to Autorecover.

    When I open your file in post #15, Excel looks for the data in C:... like you describe. I frequently see this with files downloaded from this forum. My guess is that, because the folder in your original link is not present anywhere on my computer, Excel defaults to a "roaming" folder (which doesn't exist on my computer either), but it doesn't know what else to do I guess (and -- leave it unchanged is not an option for Excel for some reason).

    I note that, when I look at the xml for your file (select xlsm file -> rename -> change extension to .zip -> then extract .zip file and browse the xml code), I find that the change to C:... occurred before you uploaded the file to the forum.

    My own experience has been that I rarely see Excel spontaneously change the path to links like you describe (except for when Excel invokes autorecover after Excel crashes). I would guess that, at some point in your use of the file, either something triggered autorecover, or you opened the file on a different computer or something that caused Excel to be unable to find the linked file, so it defaulted to your local roaming folder. In order to help you, we would probably need to know exactly how you are using the file when Excel does this to you. Until you can identify a common trigger that causes this so you can describe it to use, I'm not sure how much we can help.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  19. #19
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    It is very mysterious. I only open these files on the one computer. All of the files are located in the same folder on my D: drive and I can't think of anything I do which would change the links.

    Perhaps i should see if I can find the phantom files in the C: drive roaming folder and delete them. It also occurred to me that I could create a macro which opens and closes the linked files. If that didn't make a difference I could create a macro which enters the links anew each time I click on the new macro button (which I would label "Update"

  20. #20
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Excel spontaneously changes formula linking two workbooks

    An update. In an attempt to solve my problem I created a macro which recreates the two links each time it is run. While washing up this morning a possible reason for the problem popped into my head. When setting up the links I have always opened the second workbook by selecting it from the list of recently opened workbooks which appear when I click on Files Open. It occurred to me that Excel might be confused if, days later, it didn't find the desired workbooks in the recently opened files list. As well as the aforementioned macros, I have now linked to the other workbooks by selecting them directly from My Computer. I will see which of the methods work (if either does).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel is spontaneously switching files\workbooks
    By Caladan607 in forum Excel General
    Replies: 5
    Last Post: 04-29-2014, 10:24 AM
  2. Linking To different workbooks using formula's
    By deanmoakes in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-13-2010, 02:27 PM
  3. Linking workbooks within =countif formula
    By Degauss007 in forum Excel General
    Replies: 0
    Last Post: 04-19-2007, 04:49 PM
  4. Replies: 1
    Last Post: 05-18-2005, 03:42 PM
  5. Help needed with formula linking two workbooks
    By dcleave in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2005, 12:36 AM
  6. Help need with formula/macro linking two workbooks
    By dcleave in forum Excel General
    Replies: 0
    Last Post: 05-04-2005, 10:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1