Linking table contents-Formula for Dynamic Tables

[rks]

Hi,


I have two tables one in sheet 1(table 1-Controlling table) and another in sheet 2( table 2).

Kindly see the attached sheet:


https://www.dropbox.com/s/wzvljbi7mhzt2nn/table.xls


The Count column in table 2 has sumproduct formula eg: =SUMPRODUCT(($A3:$A16>=DATEVALUE("01-Jan-12"))*(A3:$A16<=DATEVALUE("31-Mar-12"))*($P3:$P16="India")) to count number of Indian clients


Kindly let me know how can i link these two tables such that any addition or deletion of country in table 1 is reflected in table 2 and the formulas too adjust automatically.

 

[Luke M]

What if you created a PivotTable instead, based on Table 1? With date in column heading, filter down to appropriate range, put countries in row field, and count of countries in data field. This would then easily handle adding/deleting of countries.

 

[rks]

Hi Luke,


I am not clear on the pivot table could you kindly let me know what needs to be done on the scenario shown in sheet. Also does pivot table consider formulas

 

[Luke M]

A PivotTable can be created from the Insert Ribbon. It's a handy tool for consolidating a large amount of data, and lets you quickly filter/sort the info contained within it. While it does have some formula capabilities, it's mainly an output type tool. For your scenario though, you really are filtering on a date, so I'm not sure a formula is needed.


Assuming your data has a "Date" and "Country" field, you will want to put the Date in the column area (filter as desired), Country in the row area, and another Country in the Data area (adjust field setting to show 'count' if it doesn't do that by default.