Excel 2007 : Lookup list needs to change with new sheet.

**graeme27uk** · 08-23-2011, 06:32 AM

1)
The various "TR " sheets are not working quite as I'd like.

For example in cell B1 I can select what Race I want to be. This should then create a race-specific list for eash position in C3:C18. This works fine for sheet "TR 1" but not for "TR 2 - 10". These sheets feed from information from TR 1 whereas they should be independant of each other.

2)
If I want to create a new TR sheet then is it possible to do so and have it add the new team name and race to the list for "Team Stats".

At the moment I copy an existing TR sheet and then drag down the formulae on the "Team Stats" sheet and then simply replace any reference to TR with an appropriate reference.

**Marcol** · 08-23-2011, 06:58 AM

Graeme

This thread is the same question as post #5 from your last thread
How do I remove "empty" cells in a range?

5)
I will need to also create about 20 new sheets that are exactly the same as "TR1" so that eventually I end up with TR20 or however many I need. As such I need each "TRx" sheet to be independant of each other.

You have not answered it. Do you intend to leave it hanging?

**graeme27uk** · 08-29-2011, 01:08 PM

http://www.excelforum.com/excel-2007...n-a-range.html

The bits from above have been solved, but in doing so have created new issues as detailed above.

I've attached the latest version of the workbook if people want to look at it.

**Marcol** · 08-29-2011, 01:46 PM

The new workbook has in Sheet "TR 1" Columns AH "BASIC STARTING SKILLS".

Does this mean that Sheet Player Data skills do not change once set up?

If Sheet "Player Data" skills needs to change then what rules do the changes work to?

Is it possible for an "Amazon Linewoman" to have different skills for different teams?

e.g.
You have "Chaos Warrior" selected 4 times in your sample, and it looks like you can add different skills to each of these "Positions".
How should that work?
How do these changes reflect in Sheet "Player Data" skills?
Can, for example a "Chaos Warrior" also lose skills?

By changing the way you have for selecting skills in Sheet "Player Data" it is possible to add the same skill several times, the previous method you used based on "x"s prevented multiple like entries.

If you are to get help on this project you must take it step at a time, solve/clarify each step in turn.
There are to many unknowns to expect someone new to your game to guess at.

Happy to help, but help us help you.

**graeme27uk** · 08-29-2011, 05:31 PM

Sorry, I just get carried away with a project and get ahead of myself.

The new workbook has in Sheet "TR 1" Columns AH "BASIC STARTING SKILLS".

Does this mean that Sheet Player Data skills do not change once set up?

If Sheet "Player Data" skills needs to change then what rules do the changes work to?

Is it possible for an "Amazon Linewoman" to have different skills for different teams?

e.g.
You have "Chaos Warrior" selected 4 times in your sample, and it looks like you can add different skills to each of these "Positions".
How should that work?
How do these changes reflect in Sheet "Player Data" skills?
Can, for example a "Chaos Warrior" also lose skills?

By changing the way you have for selecting skills in Sheet "Player Data" it is possible to add the same skill several times, the previous method you used based on "x"s prevented multiple like entries.

The way the game works is that each player has the starting skills as stated in the Player Data sheet. These cannot change and are fixed. A coach then has a race-dependent list of players to choose from (so an Amazon team has different players to a Dwarf team for example).

However, once a team is selected, the players in an individual team may gain skills as they progress through the game. So that a single Amazon Linewoman may end up gaining 3 skills that will be unique to her. This works fine at the moment for TR1 as I got round the issue using the code you originally wrote for concatenating the skills.

So it now works that on TR1 I can use the drop-down menus in the New Skills columns and they are added to the skills that player has. This has no effect on Player Data.

The main contention now is that everything is based on TR1 solely. If I go to TR2 (or any of the other TR sheets) for example, if I change the Race then it does not update the Players drop down menu, it still goes from TR1.

Apart from that I think I'd be happy with it working as is.

There are going to be tweaks that I might come up with, like the star player thing, though that is really icing on the cake.

**Marcol** · 09-04-2011, 02:29 PM

Okay, try this workbook.

The problem with the drop-downs was that the code for the changes was in the worksheet module for Sheet "TR 1", the code doesn't get copied over when you copy the sheet.
The code is now in the Workbook Module and will trigger for all sheets with names beginning "TR " (3 characters)

You have used VLOOKUP() a lot on unsorted data, this will eventually throw up errors.
Rather than sort all the data, in many cases it isn't possible, I have changed these to INDEX(MATCH()).

By rearranging some of your data to match your table data we can replace some of your more complicated formula.
e.g.

Please Login or Register  to view this content.

Can be replaced with this array formula

Please Login or Register  to view this content.

Checkout the new formulae.

For adding Skills I have split the skills into categories, this should make selection easier.
Select the Skill Category from AO1 then from the drop-downs in the table.

Let me know how this goes.

**graeme27uk** · 09-05-2011, 04:38 PM

Attachment 120386Hello,

Thanks for the help again. The TR sheets work really well now regarding changing the race.

One thing I noticed. Dwarf and Chaos Dwarf give the same list of players and some of the elf lists did the same thing, I think I have fixed this though.

Few questions though...

If you compare Workbook 3.2.1 and Workbook 3.3 with regards to the the Player Data. In [3.2.1] I changed it so that I had a list of skills to select from when setting up the database. I did this so that I could easily add in or delete skills as appropriate/needed.

With regards to the Player Data in 3.3, would I be able to do this still just by inserting or deleting the skill column as appropriate.

How can I tidy up the Team Summary sheet?

On the TR sheets I seem to have broken columns P3:P18, Q3:Q18, and R3:R18.

Would it be possible to "remove" column AJ3:AJ18 or shall I just hide it to make things look nice?

On the Team Summary sheet at the end, is it possible to automatically enter a new row if I need to create another TR sheet? Currently I copy the formulas down and replace the TR X with the appropriate number TR sheet.

**graeme27uk** · 09-10-2011, 07:56 AM

I think the workbook is almost there!!!

However, when sent to other people, the macros and fomulas do not work. So for example the skills are returned as ,, rather than the skills.

Other things that could be added:

1) Would it be possible to have some functionality whereby if a new TR sheet is needed that you can just click or do something simple and it automatically adds a new TR sheet and then to the team name to the "Team Summary" sheet.

2) How could you do it so that you could find the best player, say for scoring TD, from ALL the team sheets (TR sheets)?

3) Is adding/deleting new teams, skills, players easy?

**Marcol** · 09-11-2011, 02:16 PM

Sorry I can't look at this just now, will do soon.

Meanwhile add this to the Workbook module

Please Login or Register  to view this content.

That should help with the ",,,," problem.
I don't know why this error occurs, but it does.
That should at least give a temporary "fix" until I discover why it occurs.