Custom list based on value

[djg1991]

Hi guys, it's been a while since I've used Excel and now I'm racking my brains trying to remember how everything works.

Basically I would like a custom list in column B of the lineup sheet for all players in the squad sheet who match the given position. I would also like the age, overall and potential of the selected player to carry over to the lineup sheet.

I used to be able to do this kind of thing easily but I no longer work with Excel on a day-to-day basis and so I was hoping the kind folk on this forum may be able to point me in the right direction

Thanks in advance,

djg1991

[dflak]

Do you want to pick the lineup yourself, or do you want the program to pick a lineup based on Potential or Rank?

[djg1991]

I'd like to pick the lineup myself. Haven't gotten my head around the ranking column yet, but ideally I'd like it to rank the players within their position - i.e. GK#1, GK#2, CB#1, CB#2 - although this would be purely for my own benefit.

[dflak]

Here you go. I'm certainly glad you have the squad in a table. That makes Excel life so much easier as pivot tables and formulas that reference tables don't have to be changed when the size of the table is changed.

If you look at the pivot lookups, you'll see that there are a series of pivot tables each with a filter for the position. If you change the squad data, refresh any one of these pivot tables and the rest will follow suit since they are all based on the same data source.

Each one of the pivot tables is overlaid with a named dynamic range called List_GK, List_CB, etc. If you want to know how this is done, see this article: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges. Or you can take it on faith that the lists will expand and contract based on the number of players available for the position.

The lists are used for data validation in column B on the Lineup Sheet and the other cells use VLOOKUP to get the information based on the player selected.

[djg1991]

Thanks man! Any idea how I can go about getting the rank column to work automatically?

[dflak]

Just one more Vlookup.

[djg1991]

Excellent, thanks again!