The Core Add-in has an ‘Edit Mode’ that shows you where your Quick Queries and any cell references have been placed (for start and end dates, for example). When Edit Mode is ON, the add-in places Excel comments on all the referenced cells.
Special Caution For Core Add-in Users
Please upgrade to v5.2.0 or higher (open the License wizard to see what version you are running). The Edit Mode is a feature inherited from the Basic and Standard Add-in, and it was designed to work with the Quick Queries. Using Edit Mode while working with Macros in the Core add-in can result in some unexpected (and invisible) macro changes. In v5.2 and higher, Edit Mode will turn itself OFF when you open the Task Pane, and it will close the Task Pane when turned ON.
Editing the Worksheet (Edit Mode On)
If you are going to move things around on your worksheet, rename a worksheet, or insert rows or columns, turn the Edit Mode ON first. After any worksheet change, be sure to select a worksheet cell after the change, and check the comments to make sure that the add-in has re-synchronized them to point to the new cell locations. The comment should contain the new cell location. Do NOT change the comments yourself — the add-in needs to do that to keep things aligned.
Turning Edit Mode Off
While Edit Mode is on, the add-in will intercept and process every click you make into the worksheet, looking for changes. This will slow things down in larger workbooks. Turn Edit Mode OFF when you are finished moving things around. It is not necessary to have Edit Mode on to Refresh a workbook, only if you want to move a query or cell reference to a different location or change worksheet names.
Recovering From Changes With Edit Mode Off
If you happen to make changes to your workbook when Edit Mode is off, and all the cell references are still pointing to the old locations, there are 2 paths to resolution:
- Reverse your changes. In this scenario, turn on Edit Mode briefly to see the comments. Turn Edit Mode off before making any changes. The objective is to put the commented cells back where they came from, so if a comment references Sheet2!A23, turn off Edit Mode and rename the sheet to Sheet2, and move the data (inserting or deleting rows or columns) so the cell is in position A23. When you are finished, turn Edit Mode on before re-doing the changes. Remember to click a cell at the end and check a couple of comments to make sure they are in sync.
- Update the references. This could be simple or very tedious, depending on how many queries and references you have, but go through all of your queries and macros to edit the worksheet and cell locations used.