+ Reply to Thread
Results 1 to 6 of 6

Dynamic lists (Using names)

  1. #1
    Registered User
    Join Date
    03-19-2010
    Location
    Felixstowe
    MS-Off Ver
    Excel 2003
    Posts
    34

    Dynamic lists (Using names)

    Hi.

    I am trying to create a dynamic list which uses a name to determine the range of a vlookup.

    e.g.
    Workbook1
    Col 1..............Col 2

    Susan..............Skill 1
    Susan..............Skill 2
    Susan..............Skill 3
    John.................Skill 1
    John.................Skill 3
    Eric..................Skill 2

    Workbook 2
    Name....... Skill 1.... Skill 2.......Skill 3
    Susan...... Skill 1.... Skill 2.......Skill 3
    John........ Skill 1.... N/A.......... Skill 3
    Eric......... N/A.......... Skill 2...... N/A

    As i said the Vlookup I have works but I have to manually change the range, and when I have over a hundred different names it can get a bit..... time consuming.

    Any help would be great.

    Thanks

    James
    Last edited by English_Bloke82; 03-30-2011 at 05:03 AM.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Dynamic lists (Using names)

    Hello,

    you could create a pivot table. The names would run down the first column, the skills would run across the top of the columns and the table cells would show a 1 or a 0, depending on whether or not the skill is present for the name. You can suppress the zeros with formatting. You can create a secondary non-pivot table that uses the pivot table as its source and will be much easier to manage than building the table you describe with formulae off your original data set.

    cheers,
    Last edited by teylyn; 03-30-2011 at 05:23 AM.

  3. #3
    Valued Forum Contributor
    Join Date
    05-19-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: Dynamic lists (Using names)

    you could try this ---- it's not exactly what you need but it may be close enough
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-19-2010
    Location
    Felixstowe
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Dynamic lists (Using names)

    I like that idea! Will give a go thanks

  5. #5
    Registered User
    Join Date
    03-19-2010
    Location
    Felixstowe
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Dynamic lists (Using names)

    Hi teylen, I have set the pivot table up with rows and columns but the cells are blank?

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Dynamic lists (Using names)

    You need to pull a value into the data area as well. This will count the items.

+ 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