The Core Add-in has an ‘Edit Mode’ that shows you where your Quick Queries are located as well as any cell references. When the Edit Mode is ON, the add-in places Excel comments on all the referenced worksheet cells. Click the button to toggle the Edit Mode ON or off.
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 (click the button so it appears darkened). 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.
Edit Mode automatically turns on when you edit a query, and turns off when you show the task pane to work with Analytics Edge macros.
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.
Updated for v10.