+ Reply to Thread
Results 1 to 5 of 5

Select different lists

  1. #1
    Registered User
    Join Date
    12-23-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    33

    Select different lists

    Hi,

    is there a way to instruct Excel to choose between two different lists of items, that I would create (I assume) on a separate sheet?
    Essentially I have two different lists of tasks relevant to two scenarios. So first of all, I guess I would need to create a button or drop-down cell which allowed me to select between the two options (I know how to create the drop-down lists & I'm sure I could find an easy tutorial to help me create the buttons if needs be).
    The bit I'm stuck on is... so let's say I select "option A" using the the drop-down list/ buttons; I want it to then populate the cells below with the relevant set of tasks that I have created. And the other set of tasks if I select "option B".

    Is this possible?

    Thanks for any help that you can offer,
    Jonathan

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,401

    Re: Select different lists

    Sheet2 Column A has your "Option A" list starting in A1 on down
    Sheet2 Column B has your "Option B" list starting in B1 on down

    On the destination sheet, Cell A1 has the dropdown list with two options; Option A, Option B (case sensitive)
    When you select from the dropdown list, the code will automatically copy the selected list to cell B1 on down.

    To install the code:
    • Right-click on the sheet tab that has the dropdown list
    • Select View Code from the pop-up context menu.
    • Paste the code from below in the worksheet's code module.

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 06-16-2018 at 11:40 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,570

    Re: Select different lists

    Or you can try it like this...
    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Registered User
    Join Date
    12-23-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    33

    Re: Select different lists

    Thank you for this very descriptive & helpful replies. I will give these a try & comment back.

    Thanks,
    Jonathan

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,749

    Re: Select different lists

    INDEX() has a little-know, and seldom-used 4th argument that will let you choose from different lists. This is basically what you can do - tweak it as needed...
    A
    B
    C
    D
    E
    F
    97
    Range1 Range2 Range3
    98
    index from 3 different ranges aa
    123
    123
    567
    99
    bb
    234
    234
    2
    Range1
    100
    cc
    345
    345
    567
    Range2
    101
    dd
    123
    456
    range2 Range3
    102
    ee
    234
    567
    567
    103
    ff
    345
    678
    104
    gg
    123
    789
    105
    hh
    234
    890
    106
    ii
    345
    901
    107
    jj
    123
    999

    E98=INDEX((B98:D100,B101:D103,B104:D107),2,3,E99)
    E99=range selection

    these are using named ranges...
    E100
    =INDEX((range1,range2,range3),2,3,E99)
    E101=range selection

    Finally an option to select the range name...
    =INDEX((range1,range2,range3),2,3,MATCH(E101,F99:F101,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Using Data Validation to select one of five lists of names.
    By jaljr in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-17-2017, 02:20 PM
  2. [SOLVED] Shortcut to select data specified in lists
    By calum h in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-10-2015, 06:33 AM
  3. [SOLVED] select the relevant data for use from three lists
    By shaz0503 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-15-2014, 12:16 AM
  4. [SOLVED] VBA code to click/select drop down lists
    By hockeyadc in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-26-2013, 12:19 PM
  5. How to use any formula with multi-select or drop down lists?
    By heatham in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-06-2010, 05:07 PM
  6. Select from lists
    By Stevep4 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-17-2008, 05:54 PM
  7. How to select one of three lists for a combo box?
    By Ingeniero1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2006, 02:33 PM

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