+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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
    59

    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
    15,428

    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
    59

    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
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,106

    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
    59

    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
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,106

    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
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,106

    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
    59

    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
    59

    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
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,106

    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
    59

    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
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,106

    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
    59

    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
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,106

    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
    59

    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

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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