Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Derive New Lists from Existing List (Excel 2000)

    Hello y'all,

    From the list below, I'm trying to derive a separate list for each department (A,B,C,& D) that returns the expert's name, and the application. Anyone have suggestions how to approach this that does not include sorting? I was playing with IF and VLOOKUP. Blank lines are unacceptable.

    <table border=1><td>Dept</td><td>Expert Name</td><td>Application</td><td>A</td><td>Pluto</td><td>Excel</td><td>B</td><td>Xerxes</td><td>Word</td><td>C</td><td>Polaris</td><td>Access</td><td>D</td><td>Mars</td><td>PowerPoint</td><td>C</td><td>Jupiter</td><td>MS Project</td><td>A</td><td>Saturn</td><td>Acrobat Pro</td><td>D</td><td>Arcturus</td><td>Dreamweaver</td></table>

    Thanks so much,
    Rich

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Derive New Lists from Existing List (Excel 2000)

    Select the Dept column. Then select filter from the Data Menu, then click on Autofilter in the flyout menu. You Should now have a drop down list arrow in the Dept label cell. Drop down the list and select the department you want the list for.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Derive New Lists from Existing List (Excel 2000)

    Do an AutoFilter.
    Select the Dept from the Drop down list
    Highlight and copy
    Go to a new spreadsheet or where you want your list
    Paste


    Do this for each of the Departments.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Derive New Lists from Existing List (Excel 200

    Thank you two for your suggestions about using AutoFilter.

    I am seeking a way to do this without using AutoFilter follwed by copy and paste. I envision that I could edit or add to my "master" list and the various dependent lists would dynamically reflect the data in the "master" list without further action.

    That's my challenge! :-)

    Thanks,
    Rich

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Derive New Lists from Existing List (Excel 200

    What do you plan to use the derived lists for? Are they going to be datasources for something else (e.g. listboxes) or is it for display purposes?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Derive New Lists from Existing List (Excel 200

    Dear Rory,

    They'll be for display purposes.

    Regards,
    Rich

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Derive New Lists from Existing List (Excel 200

    Would it be OK to have the lists with lots of error rows at the end, which you can then hide using conditional formatting?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Derive New Lists from Existing List (Excel 200

    Well, let's see where we go with your ideas, before we say no! :-)

    Thanks,
    Rich

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Derive New Lists from Existing List (Excel 200

    See attached - you can hide the numeric columns and format the error cells with a white font. Does that work?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Derive New Lists from Existing List (Excel 200

    Attached is a spreadsheet where I am using Vlookup. It is somewhat right but it isn't right. I am using Conditional Formatting to cover the error (=ISNA(A1) for formula and choosing white for the font) on each of the department spreadsheets. If someone can look at it and revise the formulas so they don't get duplicate entries maybe it would work. Let me know if I am completely wrong.

  11. #11
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Derive New Lists from Existing List (Excel 200

    So-o-o close, yet so far away! :-)

    Thanks so much

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Derive New Lists from Existing List (Excel 200

    OK, so what would need altering? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Derive New Lists from Existing List (Excel 200

    The attached spreadsheet takes Rory's spreadsheet and puts the results in different spreadsheets. I used ISERROR to hide the errors and the range I used for the master was A2:C1000 so if you want to add more than a 1000 on the master sheet then this has to be changed in all formulas. I hid column A on each sheet.

  14. #14
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Derive New Lists from Existing List (Excel 200

    Dear LindaR, and all,

    Thank you for your very kind help. LindaR's solution will, I think, meet our needs.

    From a learner's point of view, though, I'm wondering if you could help me understand what's going on in the formula?
    <hr>=INDEX(Master!$B$2:$B$1000,SUM($A$2:A2))<hr>
    Particularly, your use of SUM as the second INDEX argument.

    Again, thanks for your help,
    Rich

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Derive New Lists from Existing List (Excel 200

    Since this is Rory's solution (all I did was adapt the formulas to separate sheets), he is better able to explain what the formulas are doing. If you unhide column A on each sheet, there is a formula there that may explain the SUM($A$2:A2)) part of the =INDEX(Master!$B$2:$B$1000,SUM($A$2:A2)) formula.

Page 1 of 2 12 LastLast

Posting Permissions

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