When using the "Excel Link" projection method the links PlanGuru makes to the Excel workbook are absolute and can be disrupted if changes are made to the underlying Excel Workbook.
Two types of changes can disrupt the links to your Excel Workbook.
The tab order changes:
- In this example I've linked to the tab "Linked Sheet" which is the first worksheet of the Excel workbook
- After creating the links I decide to alter the Linked Excel workbook. In doing so I change the position of the "Linked Sheet" tab from 1st to 3rd.
- Next, after making changes to the Excel workbook I go back into PlanGuru and try to "Refresh Excel Links" but my values won’t update correctly.
- To investigate, I click on the "Linked Test Category" and click the change links button.
- When the Excel Link interface is open, you'll notice that the focus is no longer on the correct tab. When I try to put the focus on the correct tab ("Linked Sheet") I get the following error message.
This is a direct result of the changes made to the Excel workbook documented in step 2 above.
- Restore the links to their original state by returning the "Liked Sheet" tab to its original position within the Excel workbook (3rd tab to 1st).
- You should now be able to refresh and update your links without issue.
Cut and Paste Cells to different location
- Using the same example above, I'll make a different kind of change to the Excel workbook. Instead of moving tabs, I'll move data. Here I've taken my linked data range in Excel and cut and pasted it several rows down.
- Next I try to Refresh my Excel Links, the amounts won't get updated.
Also, if I use the change links button to go and update my links, you'll notice that the numbers I've linked are no longer highlighted in red.
- To correct this you will either have to move your Excel Data back to the original location or update your links.
- Or you can relink the cells by highlighting your range, clicking the "Add" button and checking off the appropriate time periods.
PlanGuru will notify you that each period is already assigned and will prompt you to accept or reject the changes.
