![]() |
Avoid Update Values Prompts
On Sep 9, 10:31*pm, "Otto Moehrbach"
wrote: Alan * * Do you mean that when you copy a formula from one workbook to another that a link is made to the old workbook? *If so, the cure is to not copy formulas. The idea is to change the formulas so that they are no longer formulas, then copy them, then change them back to formulas at the destination. * Not exactly, but your approach may still work. When I copy the formulas, the formulas appear correct in the destination workbook (not referencing the source workbook). The formulas reference worksheets that did not exist in the source workbook but do exist in the destination workbook. So, once I copy them, it asks to update their values. Alan |
Avoid Update Values Prompts
Do you know of another way to do this? Alan
|
Avoid Update Values Prompts
Otto,
Your idea did work. Finally got rid of the prompts. Alan |
Otto,Your idea did work. Finally got rid of the prompts. Alan
I know this thread is old, but I need to revisit this!
I am building an Excel document that pulls data from fields in other documents via links. These other documents are created for me each day by my employees, so in the master document I'm creating there is a tab (workbook) for each employee and many cells on each tab that link to the corresponding document for that day for that person. With 12 employees there are many source documents, and therefore, many many links. I want to create the master document for the year in one sitting and get it done, but some of those source documents (any beyond today) have not been created yet. So when I try to copy and paste a tab in the sheet to make a new tab for another employee, I get hundreds of "Update Values" error pop-ups that I have to hit the Cancel button for each time or the red "window close" button in the upper right corner of the pop-up. Either way, my hand is starting to hurt and I'm getting super frustrated. The reason I'm attaching this question to this thread is that I tried the above solution, but as soon as I replace the "$$$$$" with the "=" again, I get the same volume of "Update Values" pop-up boxes. Either way, it's a link searching for a file that does not exist (on purpose). Isn't there any way to disable Excel's desire to "Update Values" until I'm finished finding/replacing everything? After all, when I open the document, it updates all the links in one fell swoop which would be MUCH easier and much preferred than this way! Thanks in advance. On Wednesday, September 09, 2009 9:47 PM Alan wrote: After I copy cells (including formulas) from one workbook to another using VBA (InWS.Cells.Copy OutWS.Cells), I get multiple prompts to "Update Values". Is there any way to avoid this? The original workbook contains formulas, many of which are not valid in its context, but they are used in the destination workbook. Thanks, Alan On Wednesday, September 09, 2009 10:31 PM Otto Moehrbach wrote: Alan Do you mean that when you copy a formula from one workbook to another that a link is made to the old workbook? If so, the cure is to not copy formulas. The idea is to change the formulas so that they are no longer formulas, then copy them, then change them back to formulas at the destination. For instance, in the source workbook, select the range that incorporates all the formulas you want to copy. Then do Edit - Replace, and replace all the equal signs with $$$$$. That changes all the formulas to simple text. Copy/paste. Then, in the destination workbook, do the opposite with Edit - Replace. Done. Otto On Saturday, October 24, 2009 11:57 AM Alan wrote: wrote: ther y Not exactly, but your approach may still work. When I copy the formulas, the formulas appear correct in the destination workbook (not referencing the source workbook). The formulas reference worksheets that did not exist in the source workbook but do exist in the destination workbook. So, once I copy them, it asks to update their values. Alan On Saturday, October 24, 2009 11:58 AM Alan wrote: Do you know of another way to do this? Alan On Saturday, October 24, 2009 11:58 AM Alan wrote: Otto, Your idea did work. Finally got rid of the prompts. Alan Submitted via EggHeadCafe SharePoint 2010 Using External List http://www.eggheadcafe.com/tutorials...rnal-list.aspx |
Otto,Your idea did work. Finally got rid of the prompts.Alan
P.S. I wish there was a "Cancel All" button!
- Pauline Submitted via EggHeadCafe SQL Server Table Valued Parameters / Types - Multiple Row Inserts http://www.eggheadcafe.com/tutorials...w-inserts.aspx |
Otto,Your idea did work. Finally got rid of the prompts. Alan
Hi Pauline, I know I probably do not write to you but to anyone who reads this thread some time in the future... I spend some hours not finding a solution until it dawned on me. For me Excel prompted me to update values because there was a typo in the link path I was trying to link to, without me noticing this. Hope this can help somebody out there. |
All times are GMT +1. The time now is 08:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com