How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9666
Experience:  Bachelor of Science - Engineering Consultant
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

I recently upgraded from XP to Win7. I use Excel 2007. I have

This answer was rated:

I recently upgraded from XP to Win7. I use Excel 2007. I have a pricing spreadsheet that's linked to a database on another spreadsheet. It used to automatically update when I opened the pricing spreadsheet using XP and continuously while I used it but now in Win7 the Edit Links box comes up when I open it and I have to manually hit the update values button. Then I have to manually do that again when entering certain cells that would have automatically loaded the info in XP (there's no rhyme or reason to it, it automatically loads sometimes in some cells and comes up with a N/A message in the cell).
Also, at times I need to open the database ss while running the pricing ss. In that case it refuses to update unless I close the database.
Is this a permanent issue or is there something I can change?

space2009 :


space2009 :

It sounds like you do not have "auto updating" turned on in Excel setup.

space2009 :

Try this: Click the Office Button > Excel options (at bottom) > Formulas (left menu) > Under Calculation options > Automatic

Let me know if that works for you.


Already checked that and it was always set on automatic.

space2009 :

Also try this close all Excel applications and files, then start Excel 2007,

1- click the Office button
2- click Excel Options button
3- in the left pane click Trust Center
4- click Trust Center Settings button
5- in the left pane click Macro Settings
6- select "Enable all macros...".


Had that activated also

space2009 :

On the Calculation tab, the Update remote references check box is it checked..?



space2009 :

Let me open this Question for other Experts as I am out of Ideas


OK Thanks. I've been using Excel for 18 years now and this has me stumped.



I was asked to try to help you with this issue


First can you check the Status Bar at the bottom of the Excel window and make sure that the phrase "Circular Reference" is not showing there



John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9666
Experience: Bachelor of Science - Engineering Consultant
John D and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 4 years ago.
That's not an issue. The problem is that the SS does not update automatically like it did when excel ran in XP. From what I've seen on the web other people have had similar issues and nobody's figured it out. And of course, checking with MS is pretty much useless.

Ok please check and fix ALL these settings


1) Office Button > Excel Options > Advanced, then under 'General' UNCHECK the box "Ignore other applications that use Dynamic Data Exchange (DDE)"


2) Office Button > Excel Options > Advanced, then under 'When calculating this workbook' CHECK the box "Update links to other documents"


3) Office Button > Excel Options > Trust Center > Trust Center Settings button > External Content tab, CHECK the box "Enable automatic update for the workbook..."


Hope this helps. If I was able to answer your Question please go ahead and RATE this answer "Excellent" or "Good" so I get credit for my work.


Customer: replied 4 years ago.
All the settings were the way you described in all the workbooks. I'm beginning to think that somehow MS changed something in Win7 that affects how these sheets work together; perhaps the law of unintended consequences when they rewrote the code. I am perplexed, though, as to how a change from XP to Win7 could affect excel this much.
Customer: replied 4 years ago.
John - no hard feelings here but this issue is deeper than changing a few settings. I've been working with Excel since the mid-90's and am pretty familiar with the standard workings. This issue seems to be a programming glitch. I'm just perplexed as to how changing from XP to Win7 could cause this issue in excel. Any thoughts?

Ok no problem don't take my advice and don't pay for it, but why leave me a negative feedback (which will sit on my record) when I gave you that much of my time gratis




Customer: replied 4 years ago.
I'm sorry about that. Your service as regards XXXXX XXXXX and thoughtful response was good but the answer didn't help me. The rating is based off this company's statement: "Select the face that
best describes your Expert's service." While your service was good the answer was not helpful at all and I expected to get more input from you or others and didn't get that. For $69 I expected more than standard answers I can get myself from the Excel Help menu. So to me in that regard the service was essentially useless. However, if there is a way to retract the rating I'll be glad to do it.
Customer: replied 4 years ago.
Relist: Other.
The answers, which are pretty much the standard "is the TV plugged in?" types of answers do not address the issue and I need better info.

Hi, I’m a moderator for this topic and I wonder whether you’re still waiting for an answer. If you are, please let me know and I will do my best to find another Professional to assist you right away. If not, feel free to let me know and I will cancel this question for you. Thank you!

Customer: replied 4 years ago.
I'd love some help on this but the answers from the 2 guys who responded didn't help at all. If there is someone who is more familiar with WIn7 and Excel and the changes that Win7 caused to Excel then please have them respond. Otherwise I might as well cancel since this may be bigger than your experts can handle. Hopefully you have someone because $69 is a lot to pay for answers I could have gotten from an MS help menu and had already addressed myself.

Sometimes, finding the right Professional can take a little longer than expected and we thank you greatly for your understanding. We’ll be in touch again shortly

Customer: replied 4 years ago.
Sounds good. I appreciate your efforts.
BTW - I rated John "poor service" not because his service was poor but because the answer was poor. He did the best he could with the knowledge he had. So his service wasn't poor but the answer came up short and I wish I could retract the rating. To my mind It's an unfair rating system and you might want to consider offering 2 ratings - one on the service effort and another for the usefulness of the answer. Kind of like "you get an A for effort but a D for the answer."
  • Can you provide the specific folder/file paths for the documents involved?
  • And examples of the actual links as shown currently by the main document.
Customer: replied 4 years ago.
Here's a typical formula in the dependent worksheet:
(The slave is "CABINET DATABASE JAN 2012.xlsx)

=IF(F382>0,(VLOOKUP(F382,'C:\Documents and Settings\Geoffrey Dunn\My Documents\Cabinet Guy\Cabinet Pricing & Templates\CABINET DATABASE JAN


Here's some background. I wrote a large pricing database sheet on Excel 2007 for individual custom kitchen cabinets that has standard module sizes, details and pricing. I then have a dependent master template which pulls info from that when I enter a specific module cabinet code. When I have a new job I open the template and rename it for that client. Then I enter the specific cabinet codes to come up with a sales price and material and labor costs for that job.

This program worked fine for 4 years when I ran it on XP. By that I mean it automatically updated the cells as I entered them without asking me to edit the links.

Issue 1:

>Now that I have switched to Win7 and open a template a dialogue box pops up that says "This workbook contains one or more links that cannot be updated" and offers "Continue" or "Edit Links" options.

>I click the edit button and a box pops up showing the correct workbook ("CABINET DATABASE JAN 2012" (this is the same box that comes up when you click the Edit Links tab).

>It labels this workbook as "unknown" and I have to hit the "Open Source" button and click on the correct file. Occasionally it actually recognizes it.

>If I happen to have the slave workbook open (which I need to do from time to time to edit it) then when I click the "Open Source" button and specify the sheet the dialogue box then specifies that workbook as OPEN and it will not update. In that case I have to close it to get the dependent sheet to update. (it didn't do this in XP).

SO, the botXXXXX XXXXXne issue is why does Excel need to be shown where this file is each and every time I open the dependent sheet and then also need me to intermittently hit the edit links tab again? It used to work automatically and seamlessly in XP (in fact I was barely aware of the edit links dialogue box and never had to use it).

Notes: 1) All the options in Excel are set for auto updates, 2) the "startup prompt" button at the bottom left of the edit links box always defaults to "Let users choose to display the alert or not" even though I have hit the radio button that says "Don't display the alert and update links 3) Excel is completely up-to-date and I have run diagnostics on it with no issues.

Customer: replied 4 years ago.
Are you saying that if I don't provide positive feedback you won't continue to help me?
I will continue to assist if you remove the poor rating
Customer: replied 4 years ago.
Like I already said I'll be glad to remove it if you tell me how.

The way the site works is that the only way to remove a rating is by overriding it by a better rating (it takes the higher of the two ratings)




Ok I need you to send me your both excel files.


Do you know how to zip therm together and upload them. If not, I will send you the instructions


Customer: replied 4 years ago.
Give me a refresher please

Ok for each file:


go to and upload the file using the Browse button (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.


If the file has sensitive information let me know before you upload it


Customer: replied 4 years ago.
Here you go. There should be 2 files there.

You sent only the "Farrell acct.xlsx" file



Got them. Thanks


Now please clarify which formula (i.e in which cell, on which sheet, and in which file) is not updating



Customer: replied 4 years ago.
All of them. As I said, when I open the dependent sheet the edit link dialogue page opens and I have to manually update. Then most of the time the links will automatically update but intermittently they don't and I have to manually do it again.

Which one is the 'dependent sheet'?


Just give me ONE example:


which formula (i.e in which cell, on which sheet, and in which file)


Customer: replied 4 years ago.
Please read the reply I sent at 12:10 - all the info is in there

Will do


I will get back to you as soon as I have completed the review and/or solution


Customer: replied 4 years ago.

Had to step out for a while. I just got back and will start on it now...




Ok I want you to test these files per the instructions below


1- Download the zip file

2- Save it to your desktop

3- Create a new folder on your desktop

4- Extract the two files and save them in that new folder

5- Open the CABINET DATABASE file first

6- Then from within Excel open the second file (i.e. "Farrell acct 2.xlsx")

7- Go to the 'PRICING SHEET' sheet

8- Check the formulas in M219:M290 (green cells) and test it to see if they now update as they should


Let me know


Can you provide the specific folder/file paths for the documents involved?

  • Are all the documents involved located in the exact folder path below?
  • C:\Documents and Settings\Geoffrey Dunn\My Documents\Cabinet Guy\Cabinet Pricing & Templates\

Related Microsoft Office Questions