Automate worksheet
Insert accounts, account calculations or account aggregates from the MeetingPoint into the master value column on the automated worksheet. Based on the assignment of key dates for each column, the corresponding account, account calculation values or account aggregates are inserted. The worksheet is now linked to the master values and any changes to the values are implemented directly in the table by synchronisation.
Automate worksheet
To automate a worksheet, click the “Insert” button in the “Master values” group on the “*Contents” menu bar and select “Automate worksheet” in the dialogue box. The following task pane opens:
Account group
Displays the selected account group that key date data is inserted from in the number columns. The account group can be subsequently changed for the automated worksheet.
Data type
Displays the data types that can be inserted from the MeetingPoint. At the moment, accounts, account calculations and account aggregates are available.
Actions & Options
Actions and options are available to insert master values from the MeetingPoint into the master value column on the automated worksheet.
Actions
Insert master value - Inserts the selected account, account calculation or account aggregates in the master value column. The values for the respective key dates are automatically added to the number columns.
Insert master value and description - Inserts the selected account, account calculation or account aggregate in the master value column. The values for the respective key dates are automatically supplemented in the number columns; the description is also inserted in the text column.
Keydate - Inserts the hashtag #keydate into the master value column. The key dates are automatically inserted as dates in the number columns.
Options
# invert - Activates the #invert option. If an account is inserted in the master value column, the hashtag #invert is inserted after the master value.
Numbers in the number columns change their signs. The option is deactivated again after the insertion.
# absolute - Activates the option #absolute. If an account is inserted in the master value column, the hashtag #absolute is also inserted after the master value.
Numbers in the number columns are thus displayed without signs. The option is deactivated again after the insertion.
The accounts, the account calculations or account aggregates can also be entered directly in the master value column. This also applies to the options # invert and # absolute.
Values
Lists all selected data types from the account group in the MeetingPoint alphabetically. In addition, the master values can also be found using the search bar.
Video: Automate worksheet
How to insert accounts and account calculations into an automated worksheet:
-
Open the worksheet where you want to insert accounts and/or account calculations.
-
In the master value column, select the row where the keydate should be inserted.
-
Click on the "Insert" button in the "*Content" menu in the "Master values" group.
→ Es öffnet sich ein Auswahldialog. -
A selection dialog opens in which you can now select "Automate worksheet".
→ Rechts erscheint der Arbeitsbereich „Arbeitsblatt automatisieren“. -
On the right hand side the task pane "Automate Worksheet" appears, in which you press the right button under "Actions" to have the keydate inserted automatically.
-
To insert an account calculation, select "Account calculation" in the drop-down area under "Data type".
→ Nun sehen Sie die Auswahl an Kontenberechnungen unter „Werte“. -
Now you can see the selection of account calculations under "Values". Select the account calculation to be inserted.
-
Then click on the left button under "Actions" to insert the values in the table.
-
Next, click on the row of the master value column where an account is to be inserted.
-
Select "Accounts" in the task pane, under Data type.
-
Under "Values", you can now select the account to be inserted. To find a specific account in a large number of accounts, you can also search directly for a specific account in the search field.
-
Under "Actions", click on the left button to insert the value of the account in the selected row.
Video: Automate worksheet with options
How to use the options in the automated worksheet:
-
Open the workbook with the automated worksheet in which you want to insert values.
-
Click the "Insert" button in the "*Contents" menu in the "Master values" group.
→ A selection dialog opens -
select "Automate worksheet".
-
While the master value cell to be filled is selected, search in the task pane under "Values" for the account to be inserted. The middle button under "Actions" transfers the corresponding values including the name to the corresponding row.
-
Now select the cell in which an inverted value should be inserted.
-
After selecting the correct account, click on #invert under "Options" and select the left button under "Actions" to transfer the values without text. Here the signs were reversed.
-
Next, select the cell with the average value in which an absolute value is to be inserted.
-
After selecting the desired account, click on #absolute under "Options" and select the left button under "Actions" to transfer the values without text. Here the signs were ignored.
Create account calculations and aggregates on automated worksheet
You can also create account calculations and account aggregates directly on the automated worksheet using the "Sum" button. You can access this function in the ribbon via the "*Design" tab, in the "Formulas" group.
If you select an empty master value column and click on the "Sum" button, the "Create calculation" dialog will appear. Here you can create an account calculation, or an account aggregate by summing up or subtracting master values, and insert it into the master value column on the automated worksheet.
It is also possible to select multiple master values directly in the master value column on the automated worksheet. Please note that an empty master value column must always be selected in order to be able to insert the result of the calculation. You can make a sum that is either in the first line above its summands or in the last line below its summands. If you click on "Sum", the selected master values are displayed in the dialog box.
Video: Account calculation on automated worksheet
This allows you to create account calculations and account aggregates using the "Sum" function on an automated worksheet:
-
Go to the automated worksheet
-
In the master value column, select the cells with the values you want to use for the account calculation/account aggregate.
Note: You must always select an empty cell so that the result of the account calculation/account aggregate can be inserted there.
-
Click the "Sum" button in the "Formulas" group in the "*Design" ribbon.
→ The "Account calculation/Create account aggregate" dialog appears. Here you need to define a unique name for the account calculation used, as well as select the account group in which the account values needed for the account calculation are located. -
From the list area with all account values, left-click to select the account values you want to add - and right-click to subtract. Optionally, you can also set a mathematical operator, a factor and an automatic rounding of the result.
-
Confirm the dialog with "OK".
→ The account calculation/account aggregate is displayed to you in the master value column and automatically written to the MeetingPoint.
See also
Automated worksheet
Insert automated worksheet
Edit automated worksheet
Detach automated worksheet