+ Reply to Thread
Results 1 to 8 of 8

VLOOKUP / Data Validation - Pull Data Based on Drop Down Selection

  1. #1
    Registered User
    Join Date
    03-06-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    VLOOKUP / Data Validation - Pull Data Based on Drop Down Selection

    Hi Everyone,

    I'm a novice excel user and I've been searching around the forum the last few days trying to resolve an issue with a template i'm building.

    The template will be utilized by Project Managers to track deliverables based on the phase of the project.

    I have 2 sheets - 'Master' and 'Checklist'. 'Master' will be built out to include all deliverable separated by phase. This sheet will eventually be hidden / locked. 'Checklist' has two drop down lists that I would like to pull data from the 'Master' sheet. IE - If 'Initiation' is selected in B7 within the 'Checklist' sheet, I want row 11 down to populate with all tasks in sheet 'Master' B5:518 (tasks that fall within the Initiation phase).

    I need the same scenario above to work with the second drop down (B8) that I would like to do the same with the other set of data

    Is this possible? I've attached the document for reference. ProjectChecklistHelp.xlsx

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: VLOOKUP / Data Validation - Pull Data Based on Drop Down Selection

    Here is a possible solution.
    (I am not EXACTLY sure what everything is/means but this gets the general idea across)

    ProjectChecklistHelp.xlsx

    Another option would to be have a pivot table off the MasterList tab, then instead of range A4:B8 as drop downs, you can use slicers and VERY simple index/vlookups. The slicers will control the pivot table and the index/lookups would be simple. I have started leaning towards the slicers lately...



    Let me know if you have any questions.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    03-06-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: VLOOKUP / Data Validation - Pull Data Based on Drop Down Selection

    MikeTRON - Really appreciate your help with solving this question!

    I have a few follow up questions:

    I need to incorporate the results for the B8 (Checklist) drop down as well. The results of this drop down should populate below the results returned from the B7 drop down. I cant seem to make this work utilizing the same formula type you provided. This has been the difficult part of identifying a solution.

    Additionally, I have a "Status" column in the Checklist sheet (F10). This column will not be populated with data from the MasterList. It will be utilized by the user to mark tasks as complete/in progress. Is it possible to have the status saved and linked to a task onceupdated? what will happen when a user changes the drop down value in either B7/ B8?

    Thanks everyone!

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: VLOOKUP / Data Validation - Pull Data Based on Drop Down Selection

    Quote Originally Posted by fksms2 View Post
    I need to incorporate the results for the B8 (Checklist) drop down as well. The results of this drop down should populate below the results returned from the B7 drop down. I cant seem to make this work utilizing the same formula type you provided. This has been the difficult part of identifying a solution.
    Can you to provide a better example of what you are trying to do.
    Typically people include a layout AND desired output, manually typed to prevent this sort of this because the back and forth is inefficient.

    Quote Originally Posted by fksms2 View Post
    Additionally, I have a "Status" column in the Checklist sheet (F10). This column will not be populated with data from the MasterList. It will be utilized by the user to mark tasks as complete/in progress. Is it possible to have the status saved and linked to a task onceupdated? what will happen when a user changes the drop down value in either B7/ B8?
    This will require VBA, but almost anything is possible

  5. #5
    Registered User
    Join Date
    03-06-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: VLOOKUP / Data Validation - Pull Data Based on Drop Down Selection

    Thanks for the quick reply. Please see the attached excel.

    When B7 'Initiation' is selected, all rows from MasterList that are marked as 'Initation' populate to the Checklist sheet. When B8 'Analysis' is selected, all rows from MasterList that are market as 'Analysis' populate to the Checklist sheet and are placed under the 'Initiation' rows.

    Thanks again.ProjectChecklistHelpv3.xlsx

  6. #6
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: VLOOKUP / Data Validation - Pull Data Based on Drop Down Selection

    So now you want to stack the two even though they are not related?

    I am more confused.

  7. #7
    Registered User
    Join Date
    03-06-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    4

    Re: VLOOKUP / Data Validation - Pull Data Based on Drop Down Selection

    Correct

    They are separate project management processes that occur simultaneously. This will be used by a Project Manager who is responsible for managing the PMLC / SDLC processes at the same time. This image may help explain: http://3.bp.blogspot.com/-NPqTtqyuTs...s1600/SDLC.GIF

    For example: A project may be in the 'initiation' phase of the PMLC, while in the 'Design' phase of the SDLC. I want tasks to populate within the Checklist that correlate with the current project phases. The Project manager will update the phases via the drop down options as they progress throughout the project and the appropriate required tasks should populate.

  8. #8
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: VLOOKUP / Data Validation - Pull Data Based on Drop Down Selection

    I guess my question would be why bother with the reporting front at all?
    Why not just teach them to filter on the MasterList since that would solve everything without VBA or any formulas?

+ 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. Updating cells based on selection from drop down list data validation
    By excelstun in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-18-2016, 05:11 PM
  2. displaying list based on data selection in drop down of data validation
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-16-2014, 12:51 AM
  3. [SOLVED] Updating cells based on selection from drop down list data validation
    By jingles9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2013, 02:57 PM
  4. Pull Data from Multiple Columns Based On Data Validation List Selection
    By CHRISOK in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-13-2013, 11:41 PM
  5. Pull data based on a drop down selection
    By jtd84 in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 08-30-2012, 09:16 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