You are here:

Excel/Problem changing formulas in Excel


Hi, Tom.

I'm having a problem updating a workbook that's using this formula string ~~~VLOOKUP('Jan 2014 Detail'!G4,'J:CommercialAccountOpeningQuality ControlMetrics2014Master Metrics[QA Master Jan 2014.xlsx]Errors by KYC Analyst'!$A:$B,2,0# on monthly spreadsheets.  

All I want to do is to copy the Jan ws and make Feb, March, Apr, May, etc. but when I try to update the formula to read 'Feb 2014 Detail' and QA Master Feb 2014, Excel forces me to select the workbook, even when it's already open #in the case of the second part of the formula *Master Metrics[QA Master Jan 2014.xlsx]Errors by KYC Analyst'*# or if it's the SAME workbook #the first part of the formula.#

Why is it doing that?  If I'm in a workbook and changing a link to read from another ws in the same workbook..well, there has to be something wrong with the formula, right?  I hope you can help me, Tom, there are 50+ cells with the formula each month and I really don't want to select a workbook for each instance.


Open both the old workbook that was referenced and the new workbook that is to be referenced.  then the formula would not have the path contained in the reference.  Then when you change the month, the revised formula should pick up the open workbook

Also, excel has a change links capability.  You could use that to change the workbooks referenced and the references would be changed in every formula at one time.  This works even if both (old and new) referenced workbooks are closed.  

here are the instructions from help:

Change the source workbook for all external references that use it
1. Open the destination workbook.  (the workbook with the formula above)
2. On the Data tab, in the Connections group, click Edit Links.
3. In the Edit Links dialog box, click the source workbook that you want to change.
Click Change Source.
4. In the Change Source dialog box, click the source workbook that you want to refer to. You may need to browse to find the workbook that you want.
Note that the instructions never say you have to have either the old source or the new source workbooks open.  This change is made at the workbook level. the remainder of the reference is expected to be the same in each of the source workbooks.   And this will change all formulas that refer to the source workbook.  
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.