Dynamic Link with MS-Excel

Periodical data that is entered in the data screens can be imported from MS-Excel files. The import process creates a dynamic link between the Excel sheet and specific target cell/s. Any change in the Excel file is automatically reflected in the linked data. This feature expands the usability of the software and enables you to incorporate data and calculations, which are very specific to the business, into the general layout of the software.

Excel Connection Parameters

The link to MS-Excel is activated according to parameters that are set in the "Excel Connection" tab in the Preferences screen.

The parameters include:

Activate Connect-To-Excel functionality: This option should be marked in order to apply the link to MS-Excel. When it is unmarked you will not be able to define new links to MS-Excel and any links that are already defined will not function.

Clear the cell value when the MS Excel Connection is deleted: When the link is created the value from the Excel cell will be imported into the linked cell in the specific data table. If the link of a cell to MS-Excel worksheet is deleted, the data that was imported can stay or cleared according this parameter. If it is unmarked (recommended) the data will stay and you can then edit it manually if necessary.

Enable editing of cells that are connected to an MS-Excel worksheet: This parameter defines if cells that are linked to Excel worksheet can be edited or not. If editing is allowed, changing the content of a cell will automatically delete its connection to the Excel worksheet. If editing is not allowed but required, the link to excel should first be deleted and then the cell can be edited.

Force a data update from connected MS-Excel worksheets before calculation: This parameter defines if the software will update all the data in all the screens that are connected to Excel worksheets, before calculating the plan results in order to show the analysis and the reports. It is recommended that the parameter will be marked, otherwise the analysis results may not reflect the latest changes in the connected Excel sheets.

Background Color of marked Excel connected cell: When viewing a data table in which cells are connected to Excel sheets it is possible to ask the software to color them in order to locate easily which cells are already connected. Here you can select what will be the background color of the linked cells.

Creating the Link to Excel: The link to Excel can be done when marking a cell or a range of cells in a data table, such as the Sales Plan or the Operating Expenses. The marked cells should include only cells in which periodical data is entered.

Activating the link to Excel is by clicking the "Excel Functions" button in the screen toolbar. The following options are shown in the pop-up menu that will appear:

Export the Table to an MS-Excel file: This option, that was active also in the previous version, enables to export the current data table to an MS-Excel worksheet. When clicking it the file dialog will be displayed, allowing you the give a name and select the location for the export file. Then the export file is created and loaded in MS-Excel.

Connect Cells to MS-Excel file: This option loads the connection wizard that is described below. In the wizard you select the Excel file and worksheet, and then the cells in the worksheet that will be connected dynamically to the cells in the data table.

Note: before applying this option you should select the required cells in the table

Show/hide MS-Excel connected cells: This option colors the background of cells in the data table that are connected to Excel so that they can easily be detected. A second selection of this option returns the background colors to their default colors. The background color of linked cells can be defined in the "Excel Connection" tab in the Preferences screen.

Delete connection to MS-Excel:Selecting this option will delete the connections to Excel which were defined for the selected cell or range.

Note: before applying this option you should select the required cells in the table.

Display Cell connected details: When marking a cell in the data table that is connected to Excel, selecting this option will show a screen with the details of the Excel file, worksheet and cell to which this cell is connected.

Note: The Excel Functions can also be seen in the pop-up menu that is displayed by right-clicking a cell in the data table.

Connect to Excel Wizard

This wizard starts when the button "Connect Cells to MS-Excel" is clicked. It should be clicked after selecting a cell or a range of cells that you want to link to an Excel sheet.

When the wizard is run for the first time, it will show the "Select Source File" screen that will allow you to select an Excel file, by clicking the button with the 3 dots [...] on the right of the Excel file name box. The file dialog will open and allow you to select the excel file. Then the Worksheet Name field below will show the list of available worksheets in the excel file and will allow you to select the required worksheet.

After an Excel file and a worksheet were selected you can click OK to show the Connect to MS-Excel Wizard screen.

This screen shows, in its upper section, the list of Excel files and worksheets that were already used to connect cells to data tables in the software. When you will run the Wizard for the second time, this is the screen that you will see and it will allow you to select an Excel sheet that was already used in a previous connection or to add an additional file and worksheet to the connections list.

If you want to add a file click the Add File button and the "Select Source File" screen, described above, will be displayed allowing you to select a file and a worksheet and add them to the list.

Select the require file and worksheet from the list and see their contents in the lower section of the Wizard screen. Here you can select the cells that you want to link to the cells in the data table in the software. After selecting the cells, click the OK button and you will return to the data table.

You may remove a file from the list by selecting it and clicking the "Remove File" button. This will remove the file and will automatically cancel all the connections that were made between cells in this Excel file and cells in data tables in the software.