+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    08-18-2011
    Location
    East Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    76

    Lookup list needs to change with new sheet.

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Lookup list needs to change with new sheet.

    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?
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Registered User
    Join Date
    08-18-2011
    Location
    East Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Lookup list needs to change with new sheet.

    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.
    Attached Files Attached Files

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Lookup list needs to change with new sheet.

    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.

  5. #5
    Registered User
    Join Date
    08-18-2011
    Location
    East Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Lookup list needs to change with new sheet.

    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.

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Lookup list needs to change with new sheet.

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-18-2011
    Location
    East Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    76

    Arrow Re: Lookup list needs to change with new sheet.

    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.
    Attached Files Attached Files
    Last edited by graeme27uk; 09-05-2011 at 04:41 PM.

  8. #8
    Registered User
    Join Date
    08-18-2011
    Location
    East Sussex, England
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Lookup list needs to change with new sheet.

    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?
    Attached Files Attached Files

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Lookup list needs to change with new sheet.

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1