You can define all special features of the source list(s) in forms. The forms are used
for pivot tables creation , adding subtotals, automated input, and data control. You can
create many different forms on a base of the same or different source lists. You can edit
or delete created by you the forms later. Report Wizard allows to execute different
operations using forms. You can execute these operations in the dialog window
"Operations with forms". For this purpose please: click your Excel list, run
Report Wizard and use Customize Form option.
To edit the form, please select the form and press the button "Edit Form".
To delete the form, select the form and press the button "Remove Form".
To add the new form, enter the name of a new form in the edit box and press the button
"Add Form".
To create the report (pivot table or a list with subtotals), please select the form and
press the button "Report and Quit".
To check the source list data you entered using the special tables of values, you can
select a form and press "Report and Quit" button.
If you need to find the form that has the same number of columns and
same column labels that the current source range has, please click your list and press the
"Find Next Form" button. The search is executed at the selected form to forms
list end.
Addition of the form
To add the form, it is necessary:
1. To click a list (if you have no a list, please enter column labels of new list into the
row of your worksheet, fill below at any rate 1 row of data manually. Creating the list is
over) and run Report Wizard.
2. To use the 'Customize Form' option
3.To enter name of an added form in edit box of dialog window "Operations with
forms" and press button "Add form".
The dialog window "Type of form" appears. If the flag is placed in "Create
original form" option box, the form will be created from scratch. If it is necessary
to create the form that differs a little from the other form in definition of
sorting/grouping or calculated columns, it is possible to create the form using the
description of the existing analogous form. It is necessary to place the flag in the
option box "Create the form on base of the existing one" for this purpose.
Then you will need select one to three columns controlling the source list
rows sorting/ grouping in dialog window "Form definition- Step1".
If you need to select more then 3 sorting columns, you can press "More fields"
button. In this case you can select (using the special dialog window) a column in the
right list and press "Add" button. You can drag up or down the placed columns
later or remove.
The column for operation code ( optional) and calculated columns can be
defined in the following dialog window "Form definition- Step 2". Operation code
column is the last sorting column. It is possible to select several calculated columns
(columns which contain the data for calculation using the Extended Accounting).
A placement of optional hierarchical control page number 1 (workbook and worksheet's
names) can be defined in text boxes "Checking data workbook's name" and
"Checking data worksheet's name" . This placement definition is available for
registered users only. You can set the worksheet's name of hierarchical control page
number 2 in a cell "A1" of worksheet containing the hierarchical control page
number 1.Name of hierarchical control page number 1 is optionally can be defined in cell
"A2" of the same worksheet. You can set the worksheet's name of page of
accordances number 1 in the cell "A3" of the same worksheet. What are the number
1 and the number 2 ? You can create one or two tables. Please note that two tables can be
used only for very complex accounting with debit and credit operations. Please first try
to work with only one table.
You can define name of hierarchical control page number 2 in a cell A2 of this page. Information about hierarchical control and table of accordances names is used during automated input of the source data for processing. Worksheet's name of page of accordances number 2 is optionally can be defined in cell "A3"
Name of page of accordances number 1 is optionally can be defined in cell "A2".
The structure of the restrictions table on the
hierarchical control page is described below. The top row of this table is 1. The left
column of one is "B". Hierarchical restrictions of control columns( sorting
columns and column of operation code) can be defined in this page. For example, sorting
columns of the form are "Account" and "Sub Account" and operation code
column is "OpCode" . "OpCode" codes the debit and credit operations.
Then the full list of sorting columns will be as the following: "Account",
"Sub Account", "OpCode". The first row of restrictions for control
columns has to be defined in columns "B1"-"D1". The second row- in
columns "B2-D2" and so on. All the rows of hierarchical control table have to be
sorted by all control columns. You may do not define some columns of hierarchical control
page , but all values of these columns of this page must be empty in this case.
You can create one or two hierarchical control pages. If you create only one page, the
restrictions of this page will control all rows of your source list. If you create two
pages, then hierarchical control page number 1 will control odd rows of source list and
hierarchical control page number 2- even. If , for example, you define debit operations in
odd rows and credit operations- in even rows of source list, then hierarchical control
page number 1 will control debit accounts and sub accounts and page number 2- credit ones.
The operation code restrictions will be the same in this case. If you create two
hierarchical control pages, then calculated columns values of even rows must be equal to
the analogous values of previous row. For example, debit and credit values of balance must
be equal. In this case calculated columns values of even rows are filled automatically
during automated input. .
During creation of hierarchical control pages you can use special symbols and designations
as the following:
Empty- you do not define the restrictions for this control column;
* - repeated value ;
value1-value2- value must be between value1 and value2. Example: 5-10.
value1 " > " Description. Example: 1 > Income. In this case value
"Value1" is stored only in a cell, but during automated input you will receive
the description of this value that can help you to entry source data and reduce memory
required for worksheet. Note that between "Value" and "Description"
must be special symbols: blank, >, blank. Please note that sorting the hierarchical
control page containing these symbols will be "wrong".
You can define worksheet's name of page of accordances number 1 in cell
"A3" of hierarchical control page number 1 and worksheet's name of page of
accordances number 2 in cell "A3" of hierarchical control page number 2. If you
create two pages of accordances, the page of accordances number 1 will control odd rows of
source list and page of accordances number 2- even rows. If this cell in the hierarchical
control page contains empty value, then the tables of accordances are not used. Page of
accordances may contain several tables of accordances. Table of accordances allows
defining accordances between columns of source list. This table contains 2 columns. First
column is called "main" and the second- "dependent". The table defines
accordances between main and dependent columns. For example, main column is an operation
code column and dependent column is the operation name column. If you have created this
table of accordances, then you can omit creating the "Operation name" column in
the source list, but during data input on source list you will receive the accordance
between main and dependent column that can very help you to entry the main column. If you
will omit creating the "Operation name" column in the source list ,the cell with
number of the source list dependent column ( a cell C1) in table of accordances must be
empty. If your table of accordances is large, you will receive sufficient help during the
input of a main column values on the source list.
If you create a dependent column in the source list, then Extended Accounting will check
the accordances between main and dependent columns before every report creation (if you
will choose "Control of accordances" option). If you use "Automated
input" for input of source data, then Extended Accounting will fill dependent column
automatically on the base of a main column chosen value and table of accordances you
created..
You can create several tables of accordances placed on one page of
accordances. Structure and placement of tables of accordances placed on the page are
described below.
The top left cell of the first table of accordances is "B1". In
row 1 you will need to define numbers of columns for main and dependent columns of source
list for the each table of accordances. The main column number of the first table of
accordances can be defined in column "B", the dependent column number-in
"C", the main column number of second table of accordances can be defined in
column "D", the dependent column number-in "E" and so on. The values
of tables of accordances are placed in corresponding columns below. All the rows of every
table of accordances are to be sorted by the left column.
A name of page of accordances number 1 optionally can be defined in cell "A2".
The following dialog window "Form definition- Step 3" contains two parts: result operations and source operations. Here you can add or delete the source or result operation. Each operation consists of the operation code and name. It is possible to edit the formula of the result operation or change its name. To add the result operation, enter its code and name and press Add button.
At first it is necessary to enter the result operation code and name and press "Add" button. Then instead of the dialog window result operations part you will see the created result operation code and name. Then it is necessary to define the source income and reduce operations of the given result operation. Then after pressing OK button it is possible to continue the definition of additional result operations if you need additional ones.
The dialog window appears having the forms list for definition of the base form. In this case the creation of a form is the editing of the existing form and its saving with a new name. This procedure is described in topic "Editing the form".
To edit a form, please click a cell of any list in Excel, choose "Main menu"
item in "EA" menu, select the form in dialog window "Extended Accounting
Main Menu" and press "Edit" button. Alternatively, you can choose the item
"Report Wizard", use "Customize Form" option, select a form in the
dialog window "Operations with forms" and press the button "Edit
Form".
Then you will need to select 1 to 3 source list sorting/grouping
columns for the sorting or grouping rows of a list in the dialog window "Fields for
list sorting". You can select more then 3 columns (see Addition of the form). The
column of operation code and calculated columns can be defined in the dialog window
"Form definition- Step 2" . You can select several calculation columns (columns
which consist of the data for calculation using Extended Accounting). Then also you may
define the checking data ( see topic "Addition the form"). What is the checking
data? The checking data- special tables containing a list of values for different column
types. There are two types of special tables: a hierarchical control page
( list) and a table of accordances.
The dialog window "Form definition- Step 3" consists of two parts : result
operations and source operations. There is an ability of adding or deleting the source or
result operation. Each operation contains operation code and name. You may edit the
formula of the result operation or change its name. To add the result operation, please
enter its code and name and press "Add" button.
To delete the result operation, please select the result operation and
press the "Remove" button.
To change the result operation name, click on the "Name of the result operation"
edit box and enter/edit the name.
After adding the result operation code and name, you will need to
define it's source operations that may be income or reduce. An income operation is the
operation all calculated columns of which are added to accumulated sums for grouping
columns during the result operation calculation. The reduce operation is the operation all
calculated columns of which are subtracted from the accumulated sums for grouping columns
during result operation calculation. To define the formula of the result operation,you
will need to define the income and reduce operation codes and names using "Add"
and "Remove" buttons.
To remove the form, please click on any Excel list, choose "Report Wizard" item in "EA" menu, use "Customize Form" option, select a form in "Operations with forms" dialog window and press "Remove" button.
The Report Wizard allows to create the pivot table or add subtotals to your list.
Additionally, one can automatically check your data in the list before creating pivot
table or adding subtotals. Why? To do your data more correct. Report Wizard works so: it
adds a worksheet ( data errors worksheet) into the source list workbook, copies the
selected list to the added worksheet, searches the data errors automatically on the
inserted worksheet( please see hierarchical control and control of accordances) and marks
all detected errors by red color on inserted worksheet (data errors worksheet). Please
note, that the Report Wizard does not change data on your source worksheet.
A pivot table can be created on a base of all the grouping/sorting columns and operation
code column ( if it exists) of chosen form (Please see "Calculated items"). A
pivot table also can be formed on a base of the source list columns. If the form has the
result operation(s), operation name(s) are entered into the operation code column of a
pivot table from the form description automatically. Why? To let you see them in the pivot
table. A MS Excel pivot table allows user to customize pivot table view and structure .It
allows changing grouping columns you want.Information about it please see in MS Excel help
system.
If you want to process not all rows of your list, you can filter rows of source list using
Excel AutoFilter tool before running Extended Accounting. A pivot table can be created on
base of source list. But if you filter rows of the list , Extended Accounting copies
filtered rows to worksheet it has added automatically. In this case the pivot table will
be created on the base of this worksheet.
You can use two methods of creating pivot table :
Method 1. You can add fields to an "empty" pivot table. "Empty" pivot
table is a pivot table that has no fields. It is essentially useful for pivot table
creating on base of an external data base.To create "empty" pivot table, please
click Pivot Table item in Excel Data menu, choose the source list range or external data
base (and filter data base rows if it is necessary). Then simply click OK button several
times. The "empty" pivot table creation is over. To insert fields in this pivot
table,choose "Main menu" item in "EA" menu, choose a form you need,
click "Insert Fields to Pivot Table" button. The control columns of the selected
form will be added to an empty pivot table as its fields. Creation is over.This method
does not allow to make the hierarchical control and control of accordances before creating
this pivot table. But this method allows to easy create the pivot table basing on external
data base ( MS Access, Visual Foxpro and so on).
The Extended Accounting allows to check the external data base only if you creates the
data base using Export tool. In this case please check your Excel list before export using
Method 2 or automated input.
Method 2 (of creating a pivot table). Click a list containing at any rate 2 rows of data (
if you have no this list, please use automated input for its creating), choose "Main
Menu" item in "EA" menu, choose a form, press button "Report
Wizard", do not choose "Customize Form" option( if you do not want to
change the form definition).
The dialog window "Report details" appears. In this dialog you can choose next
options:
1. Hierarchical control- allows to copy your source list to the worksheet that the Report
Wizard adds automatically (data errors worksheet), check hierarchical restrictions of
values of source list and mark them by red color before pivot table creating . This
control is available for registered users only. You can move to the inserted worksheet
(after Report Wizard termination) and find the red color values automatically using the
"Find the Source List Error" tool ( please see "Find the Source List
Error" procedure).
2. Control of accordances- allows to copy your source list to the added worksheet (data
errors worksheet) , check the accordances of values of source list columns on base of
tables of accordances and mark them by red color before pivot table creating. This control
is available for registered users only. You can move to the added worksheet (after Report
Wizard termination) and find the red color values automatically using the "Find the
Source List Error" tool ( please see "Find the Source List Error"
procedure).
3. Pivot table- allows to create a pivot table using a selected list and a selected form.
4. "Control of dates" - if to place the flag inside this checkbox and define the
date column(s), these columns will be controlled. How? If the value is not valid date, one
will be selected by red color. How can I find this wrong value if my list is large? Please
see the "Hierarchical control" option above.
When creating the pivot table, the program is checking the accordance
of the source list columns' names with the chosen form columns names. If they are not the
same, then "Report checking" dialog window appears to choose among the next
possibilities:
1. To add a new or select other form- return to dialog window "Operations with
forms". This button is enable in the customize mode only.
2. Ignore- to ignore difference of the list columns' names. In this case a source list
columns order and their purposes must be the same as in the selected form. If they are not
the same, you will create, unfortunately, a not valid pivot table. You can not need one in
this case.
3. Quit - program ends.
A pivot table you need is placed by Report Wizard on an added (by Report Wizard) worksheet
at the left of the source list.
There is the sample of source list and special lists for the
processing by Extended Accounting placed in the work book "Examples.xls".To open
the workbook "Examples.xls", you can choose "Example" item in the
Extended Accounting menu ( "Start" button, Programs, Extended Accounting). In
this example the "Department" column is the main sorting column and "Ric
Code" column- is the second column. The "OpCode" column is the column of
operation code. The codes of operations are coded in this column as specified below:
1- Incoming balance of goods;
2- Income;
3- Expenditure.
If you run the Report Wizard ( please click a cell on your list, run
Report Wizard, use "Customize Form" option), the "Goods" form will
appear in the list of forms in the dialog window "Operations with forms". In
this form the "Department" and "Ric Code" columns are chosen as the
sorting columns and "OpCode" column is an operation code column. The
"Sum" column is chosen as a calculated column.The worksheet's name of
hierarchical control page number 1 is defined in "Worksheet for checking" text
box . The resulting operation 4 - "Outgoing balance" is defined as incoming
balance + income - expenditure.
There is the worksheet "Table" in "Examples" workbook which contains
the pivot table created by Report Wizard. Also, you can find the "Errors"
worksheet in this workbook containing the source list errors. What is the "source
list errors"? During the data entry into the Excel list you can do mistake and enter
the wrong value. The Report Wizard allows automatically to copy this list to the
"Errors" worksheet and find this wrong value(s) automatically. Then it marks
every wrong value in "Errors" worksheet by red color. How? You can create the
hierarchical control page and the table of accordances. Please see more about these tables
in the topic "Input of the source data". The Report Wizard uses these tables to
find incorrect values in your Excel list for you and allows you to do your data more
correct.
You can try creating an analogous pivot table and worksheet with errors (the data errors
worksheet) as it is described in the topic "Creation of the pivot table and adding
subtotals".
There is a hierarchical control page number 1 in "Hierh"
worksheet of "Examples.xls" workbook. In this case it is located in the same
workbook. As the value in the cell "A1" of this worksheet is empty, the
hierarchical control page number 2 is not used. A name of the hierarchical control page
number 1 was defined in cell A2. The hierarchical restrictions on this page are defined
for "Department", "Ric Code" and "Op Code" columns. You can
create the hierarchical control page that does not contain the values for the extreme
(left or right) control columns on it. For example, if you do not define the values in the
"Department" column, the restrictions for the "Ric Code" and
"OpCode" columns will apply to any values in "Department" column in
this case.
Worksheet's name containing the page of accordances is defined in cell A3 of this
hierarchical control page (in this example this name is "TablAc"). There are two
tables of accordances on this worksheet "TableAc". Each table of accordances
consist of two columns: left- the main column and right- the slave or dependent column.
How does the Report Wizard can know which the column of the source list is main and which
is slave? This program sees the tables of accordances placed on the "TableAc"
worksheet. The first row of any table of accordances must contain the number of main and
the number of slave column in the source list. These numbers are calculated by the
following way: 1 for column A, 2 for column B, and so on. Where does a worksheet
"TableAc" contains the numbers of the source list main and slave ( dependent)
columns? This worksheet must contain the numbers of source list columns in row 1. Please
see the "TablAcc" worksheet for more details. The column number of main column
of first table of accordances is stored in the cell B1, the dependent column of this
table- in cell C1. In this case the main column is the "OpCode" column and
dependent column-is "Operation" column. The second table of accordances is
placed at the right of the first table on the same worksheet. The column number of main
column of the second table is stored in the cell D1, and one of the slave column- in the
cell E1.
Some errors were made by Extended Accounting author in "Operation" column of the
example worksheet "Example" on purpose .These erroneous values are not in the
accordance with the example table of accordances ("TablAcc" worksheet). If you
click the list on the "Example" worksheet and run Report Wizard, then these
values will be marked by red color automatically. How? Please open this example, click a
source list on the "Example" worksheet, run Report Wizard, choose options
"Hierarchical control" and "Control of accordances". You can see that
the new worksheet has been added and this worksheet contains the copy of a list placed on
the "Example" worksheet, but some cells have been selected by red color. Then
you can run the procedure "Find Source List error" that is designed to
automatically find the wrong (of red color) value(s) on the current worksheet. How do I
can run this procedure? Please click a cell in the first row of a list on the
"Errors" worksheet and run the procedure "Find Source List Error" (
please see "EA" menu).
The main column of second table of accordances placed on the "TableAcc"
worksheet defines the list of possible values for "Ric Code" column and the
slave (dependent) column defines the description for every "Ric Code" value.
This allows to know the accordance between "Ric Code" and its description during
the automated input. In this case, however, the source list does not contain the column
for this description. Do you want to know how can you add this column in the source list?
In this case you will need to create a new list ( with this column) and enter the source
list column's number in "E1" cell of the "TablAc" worksheet. This
column of your list will be controllable automatically in this case (with the control of
accordances tool of Report Wizard). Moreover, the automated input will allow you to fill
this column automatically basing on "Ric Code" column. How? The Input Wizard
will copy this value from the second table of accordances. Read More for additional information.
If you need financial Excel templates, you can review Financial Excel Templates - NPV, IRR, EVA, and much more. If you want to operate with your business resources, please review Operations Management - business forecasting, waiting lines, MRP, and much more.