Getting data from table into smaller tables on a seperate sheet

Hi, I was hoping someone could help me with my spreadsheet. I would like to have a "database" like sheet, in which I can easily add new or delete existing players. From that, I would have a second sheet which would show the player information in each position. So in sheet 2 the first table will have all the ST, table 2 all the AM L etc. I would also like for each table to also add the players that can play in that position under Pos 2. I have uploaded it with 2 players as an example of what I mean.

Any help would be majorly appreciated, been stuck on this for a while and can't seem to get anything to work right.

Add a simple key to the DB and it makes a lookup easier.

Column A of the Database sheet has a "count" formula in it that counts how many time the value in F on that row has appeared from the top down through that row. It creates a "key" numbering system that is unique to each item. the first time DM appears, it keys that as DM-1. The second time it appears, it keys it as DM-2. A unique key makes finding specific rows easy with lookup formulas.

Sheet1 in C2 we COUNT how many times the value in B2 appears on the database sheet in column F, so we know what the maximum number of matches we can display is.


In sheet1 A4 is another "key" formula that will display ST-1 if there is at least 1 in cell C2. In A5 it shows ST-2 if there is at least a 2 in C2, etc.

Now the rest of that first table is a simple INDEX/MATCH formula that brings over data from the same row that matches the value in column A.

Once the first section is complete, we copy I all down to the second section, then edit the first row formulas to start at 1 again.

Here is my attempt using a helper column which is a serial number which is used in INDEX AND MATCH.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.