sub-tables from a master list

[CMBell]

I am trying to create a dynamic sub-table on another sheet from a master list in excel 2007. Where the master list size can change and have this reflected in the sub-table. I cannot use pivot tables. Does anyone know how to do this?

Example:
Master List (Locations):
London
Drummond
Kentville

Sub List (should look like this):
London #count
Drummond #count
Kentville #count

Thank you

[Kaper]

you probably shall use function COUNTIF
something like in B2 of Sheet Sub List
=COUNTIF('Master List'!A:A,A2)

[CMBell]

Yes countIF would give me the count but I need to automatically populate the Location names in the sub-sheet from the master-list

[Kaper]

After http://www.excelforum.com/forum-rule...rum-rules.html :

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[CMBell]

Here is an example of what I want. The first sheet contains the location names, the 2nd sheet should be the dynamicly generated table using the location names from the 1st sheet.

example.xlsx

Any help would be appreciated

[newdoverman]

Maybe something like this will help. I don't know exactly what you are counting in the two columns to the right but the formula can easily be changed to accommodate what you want counted.

[CMBell]

That close to what I need... but is there a way to make the data table change when a new location is added to the master sheet?

[newdoverman]

Minor adjustment. Extend the sub table as required to accommodate the expansion of the Master.

[CMBell]

Can this be done automatically in the formula?

[newdoverman]

The only way using a formula is to extend the sub table that has the formulae in it to get the data from the Master. Excel does not "Push" data it draws from a source which in your case is the Master file.

You can extend the sub as far as you want to accommodate new data. If it is absolutely necessary to have the sub expand automatically, a VBA solution is the only way to do it which for the time being is not something that I can do for you.

[CMBell]

Thank you for the help

[newdoverman]

You're welcome


Thank you for the feedback.