12 July 2016
One of the nice features of the later versions of Microsoft Dynamics® NAV is the ability to send any page in NAV to Microsoft Excel, not only as a list of data as you would get with a copy and paste, but a refreshable link using the refresh button on the Dynamics NAV tab in Excel. This demos really well, and prospective customers love it, however there are draw backs, the resulting table in Excel does not allow you to add columns to do your own calculations or to do lookups to other data in the workbook. You can do PivotTables / Charts on the data, and refer to it in other areas of the workbook, but what if you want to manipulate the table as a whole? Luckily there is a solution that allows you to add columns and still retain the refresh capabilities of the workbook.
Let me start off with my golden rule of data sent to excel from Dynamics NAV.
“Never modify the NAV Datasheet.” By NAV datasheet, I mean the sheet that NAV creates when you press the Sent to Microsoft Excel button.
Why is this? Partly because you are restricted from what you can do, but mostly because it saves complications if you let NAV / Excel do their thing!