Excel Client:Data Input/Planning with the CoPlanner Excel Client
Data acquisition in formula mode
The CoPlanner Excel Client allows you to use Microsoft Excel as a front-end for entering or updating data and saving it in the database of the CoPlanner server. Use the Analyzer to define the desired data and format them as needed. Then transfer the data to Microsoft Excel (see Transferring a Report into Excel). Right-click on the inserted report to display the Microsoft Excel context menu with the extensions added by the CoPlanner Excel Client. Select “Convert to formulas”. A message box will appear. Click “Yes” in the message box to start the conversion.
All value fields will now contain the CoPlanner Excel Client read formula (CellRead). To write the data into the database, you must activate the option “Write into database”. To activate this option, please open the Options dialogue and check the check box next to this option. Then confirm your settings by clicking “Apply”. After you have done this, when a value is entered in a cell containing a read formula, the value will be applied in accordance with the filter settings when the write-protection and security settings make this possible.
Once the value has been successfully entered into the database, it will appear in the cell that was updated. Otherwise the cell will contain the string “N/P” for “Not Possible” (see Constants and Return Values). If “Automatic updating” has been activated (see Options), the cell will contain the value that was previously saved in the database.
| Hint You can open the entire dimension if you press and hold the Alt key while double-clicking on the dimension element. |
| Hinweis The function "Data inputting to database" can only be activated by the PowerUser. |
Select multiple cells when entering data
When entering data in the CoPlanner Excel Client, you can select multiple cells for data entry.
When you enter a value in the active cell, the same value will be entered in the selected cells.
Entering data with Microsoft Excel references and formulas
The CoPlanner Excel Client gives you the option of using Microsoft Excel cell references and formulas when entering data.
After creating the formula or cell reference, press the Enter key to enter the value in CoPlanner.
A cell reference can refer to the following elements in one or more formulas:
- Data in a cell on the worksheet.
- Data in different regions of a worksheet.
- Data in cells on other worksheets in the same workbook.
Aids for entering data in CoPlanner value fields
Automatic completion
CoPlanner provides the following shortcuts for entering values: To enter 100,000 EUR, you can type “100t”. By analogy, “m” can be used as a short cut for millions.
Customising values
When you press F2 in a CoPlanner value field, a data input dialogue opens.
You can use this dialogue to change values without having to enter the entire contents all over again.
| Hint When entering text, the program automatically checks to ensure that the length of the text string you enter is not greater than the length of the text field in the CoPlanner database. |
This dialogue is available both in Formula Mode and in Dynamic Mode.
Detail record view
The CoPlanner Excel Client offers you the option of viewing a specific cell from all detail records from a matrix.
Highlight the cell for which you want to view the detail records and select the command “Show detail rows” from the context menu.
The Detail Record View Assistant appears. This is a dialogue that consists of two steps. In Step One, you define the measures you wish to display.
At this point you can click “Finish” to exit the dialogue. The program will create a workbook containing the detail records of the selected cell.
In Step Two of the Detail Record View Assistant, you have the option of applying a style sheet to the table containing the detail records.
After clicking on “Finish”, a list of the detail records together with all available filters will be displayed.
| Hint The detail record view works in both Formula Mode and Dynamic Mode. |
Fix Values
This function allows you to change individual values without changing the value of the total. The remaining values making up the total will be altered proportionately. The context menu in the data area contains the “Cell” command with a flyout menu showing “Fix value over ‘...’”
Whenever you want to fix values, the dimensions of the line or column axis will be available. In this example, the column “Absolute sales” contains the value of 1,000.- for five products
The sum element for “Road Bianchi” contains the value 5,000.-. Select the command “Fix values over ‘Products’” from the context menu and then enter the value of 3,000.- in the element “928 SL IASP Super Record 11sp Double”.
After recalculation, the sum element “Road Bianchi” still displays the value of 5,000.-, while the new value of 3,000.- appears in the element “928 SL IASP Super Record 11sp Double”. The difference has been deducted in equal amounts from the other elements.
Data transfer
Data transfer is available if this function has been activated in the input mask (see the section on the Data Assistant in the Framework Manual).
Data transfer can be accessed via the context menu in the data area by selecting the “Cell” menu item.
Data Distribution
Data distribution is available if this function has been activated in the input mask (see the section on the Data Assistant in the Framework Manual).
Data distribution can be accessed via the context menu in the data area by selecting the “Cell” menu item.
This function distributes the total value among the elements lying directly below it.
