Automatic refresh of pivot table like graphs
Permalink
Bookmark
Share
Twitter
Digg
Reddit
Facebook
Stumbleupon
Del.icio.us| IP.com Number | IPCOM000177813D |
|
|
|---|---|---|---|
| Dated | Jan 5, 2009 UTC | ||
| Size | 5 page(s) (54.1 KB) | ||
| Disclosed by |
|
||
Publication Summary
Microsoft(*) introduced the powerful Pivot Tables into Excel(*) to let the user display the data contained in a column of an Excel(*) list by means of subtotals (or other calculations, user selectable). By using the useful Pivot Table, also the relationships within the data may be found (row with the same summarization key). But a Pivot Table is not directly linked to its source data. If the data in a list (from which a Pivot Table has been created) is changed, the data in the Pivot Table is not directly updated to reflect the changes, unless the refresh option in the Data pull down menu is selected, or unless a refresh macro is invoked (associated to a button or to a worksheet selection), or by right clicking the Table and then selecting refresh. Anyway a manual action is requested to update the data in the related graph. Also there is no way to order the result avoiding gaps, all by using simple native spreadsheet functions. If the user needs instead an automatic update that shows refreshed data as soon as updated in the source list (that can be contained in different external sources workbooks for example), the Pivot Table method cannot be used in an effective way. There are several applications that might need to have such a solution in place. For example: - If you are using a Pivot Table to show some data on a monitor, you would like that as soon as the source is updated the monitor will show the new data without waiting for someone that runs the refresh (or you need to put in place an automation that periodically runs the graph refresh). - If you have linked worksheets and you like that the "independent" one gets updated as soon as the dependent ones are, to have a sort of on line data checking (you might do it by asking to manually refresh the data on request, or with an automatic periodic refresh program). ================================================================================================ (*) Microsoft and Excel are trademark of Microsoft Corporation in the United States, other countries, or both| Country | |
|---|---|
| Language | English (United States) |
About this Publication
This document was submitted to IP.com's Prior Art Database and this preview is designed to provide you with information regarding the contents of this document by displaying up to the first four pages of the document as scaled page renderings and displaying a limited amount of text which was extracted from the document on the Text Preview Tab.
To find out more on how to obtain the entire document, click the Download tab. There is a charge for downloading some Prior Art Database documents; please examine carefully whether you believe this document fills your needs before purchasing.
For more information about the Prior Art Database, visit the Learn section of this website. Thank you for visiting IP.com's Prior Art Database! You may wish to check out our Global Patent Search website before you leave.
Automatic refresh of pivot table like graphs
Hereafter is proposed and described, a method that, by using some simple native and existing Excel(*) functions, summarizes a range of spreadsheet cells (in a fix way, based on the content of a corresponding range of key cells), without using the advantages of an interactive Pivot Table and performs an automatic and real-time data refresh, without user intervention, as soon as the source list data is updated.
Moreover another important aspect is the sorting of the data shown in the graph. Usually if there are gaps of data, you can avoid that the labels are shown on the final graph, you can avoid that the 0 value is displayed, but the gap of the data cannot be automaticallyand dinamically avoided. Below is also described a "native" method to address this need.
The result is obtained without writing any line of code, without using macros,
just using native
Following are listed the steps of the proposed method to obtain the summarization with automatic refresh in case of source data change:
Steps of the Method
Spreadsheet definition
A spreadsheet
(ex. SHEET1) is defined with a range of cells containing data. A column contains a value to be used as summarization key and another column (or more) contains the corresponding value to be summarized. A second sheet can be defined
source sheet
excel functions available in a worksheet.
Let us start with this example. The figure 1 shows a range of cells containing the data to be summarized and an Excel(*) Pivot table created on them:
Fig. 1
1
to divide elaboration from source and result data. Source key cells definition
In the second elaboration sheet (ex. SHEET2) a range of cells is defined containing the summarization key cells as duplicate of a range of cells present on a different sheet (ex. source sheet SHEET1). The elaboration sheet may match with the source sheet. The same elaboration can also be split in different elaboration sheets.
Ex. Sheet2!E10:E19 = Sheet1!E10:E19 as shown in Figure 2.
Fig. 2
Source values corresponding to key values definition
As in step 2, on the elaboration sheet, a range of cells is defined with the keys corresponding values from the source sheet range.
Ex. Sheet2!B10:B19 = Sheet1!G10:G19 in case of one column values. As shown in the second picture of figure 2.
Duplicate key cells entries identification
On the Elaboration sheet (SHEET2) a range of cells is defined to identify duplicate entries among the keys. It uses the IF conditional native function of the spreadsheet (as shown in figure 4) For each column the value of the key present in the cell is compared with all the other key values. For example =IF (E11=E10,1,0) meaning: if the value of the key present in the cell E11 matches the value of the key present in the comparison...