![]() ![]() In the time intelligence example, the Customer table would need to have date values that also exist in a time intelligence table. If you aren’t prompted to create a relationship, Excel already has the relationship information it needs to relate the data.Ĭreating relationships is similar to using VLOOKUPs: you need columns containing matching data so that Excel can cross-reference rows in one table with those of another table. You’ll know whether a relationship exists when you drag fields from different tables onto the PivotTable Fields list. Step 2: Find columns that can be used to create a path from one table to the next Step 1: Determine which tables to specify in the relationship “Relationships between tables may be needed” More about relationships between tables in ExcelĮxample: Relating time intelligence data to airline flight data For example, if you had a date column in both tables, you would choose that column now.įor Related Table, select a table that has at least one column of data that is related to the table you just selected for Table.įor Related Column (Primary), select a column that has unique values that match the values in the column you selected for Column. Using our customer and time intelligence example, you would choose the customer sales table first, because many sales are likely to occur on any given day.įor Column (Foreign), select the column that contains the data that is related to Related Column (Primary). In a one-to-many relationship, this table should be on the many side. In the Create Relationship box, click the arrow for Table, and select a table from the list. ![]() In the Manage Relationships box, click New. ![]() If Relationships is grayed out, your workbook contains only one table. Excel can only create the relationship if one column contains unique values.įor example, to relate customer sales with time intelligence, both tables must include dates in the same format (for example, ), and at least one table (time intelligence) lists each date just once within the column. Verify the column in one of the tables has unique data values with no duplicates. Give each table a meaningful name: In Table Tools, click Design > Table Name > enter a name. Make sure the workbook contains at least two tables, and that each table has a column that can be mapped to a column in another table.ĭo one of the following: Format the data as a table, or Import external data as a table in a new worksheet. For all other cases, you’ll need to create relationships manually. OTable2.When you import related tables from a relational database, Excel can often create those relationships in the Data Model it’s building behind the scenes. If Not IsNull(oEventListener) Then 'only if still running OTable1.addModifyListener(oEventListener) OEventListener = CreateUnoListener("EventListener_", ".XEventListener") If IsNull(oEventListener) Then 'just to be shure it doesn't start twice OTable1 = ("PivotTables").DataPilotTables.getByName("DataPilot1") OTable1.addModifyListener( oEventListener)Įxpects 0 args. In this case, the standard filter will replace the slicer.Ĭonnecting a new table will not be difficult either. OFilterDsc2.FilterFields = oFilterDsc1.FilterFields , MB_ICONSTOP, "macro:SyncDataPilotsByFilter" OTable2 = oTables.getByName("DataPilot2") 'oTables(1)ĭim oFilterDsc1 As Object, oFilterDsc2 As Object OTable1 = oTables.getByName("DataPilot1") 'oTables(0) ''' Synchronize DataPilot1 with DataPilot2 data by filter using the filter set in DataPilot1.ĭim oSheet As Object, oTables As Object, oTable1 As Object, oTable2 As Object Who knows how to intercept the data filtering event in a pivot table 1? Sub SyncDataPilotsByFilter() I am unable to add an event listener to the first table in order to automatically invoke the sync routine on the second table. Let’s say I created two pivot tables based on the same data source and applied a standard filter to one table and programmatically synchronized the second with the first one.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |