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: 9655
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

Customer Question

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?
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  LeGin replied 1 year ago.

space2009 :

Hello,

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.

Customer:

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...".

Customer:

Had that activated also

space2009 :

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

Customer:

Yup

space2009 :

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


Customer:

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

Expert:  John D replied 1 year ago.

Hi,

 

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: 9655
Experience: Bachelor of Science - Engineering Consultant
John D and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year 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.
Expert:  John D replied 1 year ago.

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 1 year 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 1 year 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?
Expert:  John D replied 1 year ago.

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 1 year 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 1 year 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.
Expert:  Rachel-Mod replied 1 year ago.

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 1 year 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.
Expert:  Rachel-Mod replied 1 year ago.

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 1 year 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."
Expert:  romr2009 replied 1 year ago.
  • 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 1 year 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

2012.xlsx'!CABINETDATABASE012812,3,FALSE))*C382,"")


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 1 year ago.
Are you saying that if I don't provide positive feedback you won't continue to help me?
Expert:  John D replied 1 year ago.
I will continue to assist if you remove the poor rating
Customer: replied 1 year ago.
Like I already said I'll be glad to remove it if you tell me how.
Expert:  John D replied 1 year ago.

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)

 

Expert:  John D replied 1 year ago.

Thanks

 

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 1 year ago.
Give me a refresher please
Expert:  John D replied 1 year ago.

Ok for each file:

 

go to http://www.wikisend.com/ 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 1 year ago.
Here you go. There should be 2 files there.

http://www.wikisend.com/download/434766/Farrell%20acct.xlsx
Expert:  John D replied 1 year ago.

You sent only the "Farrell acct.xlsx" file

 

 

Expert:  John D replied 1 year ago.

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 1 year 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.
Expert:  John D replied 1 year ago.

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 1 year ago.
Please read the reply I sent at 12:10 - all the info is in there
Expert:  John D replied 1 year ago.

Will do

 

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

 

Customer: replied 1 year ago.
Thanks
Expert:  John D replied 1 year ago.

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

 

 

 

Expert:  John D replied 1 year ago.

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

 

http://www.filesharesite.com/files/201208/1344110600Both_Files.zip

 

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

 

Expert:  romr2009 replied 1 year ago.
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\

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional