Refresh Data

It should be noted that instead of C26 you have to substitute the last line number in your table, such as C24. Drag the fill handle to the right, fill the cells in three columns. Amounts in the opening balance and turnover were calculated. For calculating the amounts at the end of the period should introduce a more complicated formula. As you remember, we are using the job number format does not show a negative amount, but the calculations negative numbers of the cells also will be summarized. Therefore, the cell amount in column G, enter the formula = SUMIF (G4: G26; '> 0').

Just as in the previous formula, you should change the G26 to the address of the last cell in the column. The formula indicates that we should summarize only the positive values of the cells. With auto-enter a similar formula in column H. It now remains to enter opening balances, and the reverse statement is ready. You can change the wiring in the transaction log, or enter a new transaction. After that you should go to the list Shahmatka, Right-click on the pivot table and choose Refresh Data menu.

Summary table will be rebuilt, while the reverse will be automatically converted statement. Based on information from the back statements, we can construct a variety of useful reports, but we do not need to do this. You can create your own standard forms in Excel, using the techniques described in the previous chapter, and add links to the cells circulating statements. When referring to a cell turnover sheet does not necessarily put the standard forms in the same workbook in which organized accounting. Excel allows you to refer to cells table located in another file. For example, if you want to insert a link to the G4 cell turnover sheet, enter the formula = 'Book uchet.xls Reverse list'! $ G $ 4. Please note that to use the names gaps need to put the name in single quotes. When opening a file with links to a table cell from another file, the dialog appears with a warning. You can update your communication, get the latest information. For this need to click the Refresh button in the dialog. In this dialog closes, and the cells appear most recent data from the accounting system. If you click Do not upgrade, then after closing the dialog information in the cells remain unchanged. This example shows that with Excel you can automate a variety of areas of accounting, and quality of automation is not worse than using a specialized accounting software. It should be remembered that the capacity of Excel is much wider, and it is possible to use programs written in the programming language Visual Basic. However, writing such programs requires some knowledge and better that those engaged in the programmer, not an accountant.

Comments are closed.