+ Reply to Thread
Results 1 to 16 of 16

Using Data Validation to select one of five lists of names.

  1. #1
    Registered User
    Join Date
    06-12-2017
    Location
    Meridian, Idaho
    MS-Off Ver
    Excel - Version 1704 - MS Office 365
    Posts
    13

    Using Data Validation to select one of five lists of names.

    Hello,
    I have five rosters of names in a Roster tab. I have a cell on a separate Report tab that calls an individual roster. Can I use data validation to choose which roster to call to this Report cell?
    I want to have two data validation areas on the Report tab. One that calls the correct roster to the other one.
    Thanks for any help.
    Here are pics of a couple of the rosters and the report:
    DV prob - Roster.JPG
    DV prob 1.JPG

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,806

    Re: Using Data Validation to select one of five lists of names.

    Hard to say without an uploaded Excel file.

    I usually do this with all the lists in contiguous columns, put unique identifying headers above each list and then in data validation use something like

    =INDEX(the range,,MATCH(ref,the headers,0))

    Is that helpful?
    Dave

  3. #3
    Registered User
    Join Date
    06-12-2017
    Location
    Meridian, Idaho
    MS-Off Ver
    Excel - Version 1704 - MS Office 365
    Posts
    13

    Re: Using Data Validation to select one of five lists of names.

    Thanks, Dave. I'll try that. Can I send you the file in a private message?

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,806

    Re: Using Data Validation to select one of five lists of names.

    Please don't. Others will benefit from your post + plus you will likely get additional response to your thread. There are a lot of good ideas out there. Please take advantage of the opportunities. That's why we're here.

  5. #5
    Registered User
    Join Date
    06-12-2017
    Location
    Meridian, Idaho
    MS-Off Ver
    Excel - Version 1704 - MS Office 365
    Posts
    13

    Re: Using Data Validation to select one of five lists of names.

    Sounds good. I'm attaching the file to this reply.
    Please see the first three tabs: Roster, Week 1, and Report 1; and the last one: Data.
    I would like to eliminate the Report tabs 2-5, and just have Report 1.
    I would like this Report tab to have 2-3 data validation lists.
    What I envision is: one list to call the correct roster from the Roster tab (located in D3),
    one list to call the correct week from the Data tab (located in C3),
    and one list to choose the correct individual from the chosen roster (located in E3).
    Ideally, I would end up w/ just 8 tabs (instead of the current 12).
    I tried to implement your formula: =INDEX(the range,,MATCH(ref,the headers,0)) - but I couldn't get that to work.
    Any help would really be appreciated.
    jaljr
    Attached Files Attached Files

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,806

    Re: Using Data Validation to select one of five lists of names.

    Well I'm not sure what I proposed is such a good idea, and there is a lot of missing information and far more DV lists than I anticipated. I am trying to find my next question(s).

    In the meantime there is one thing that definitely needs to be done. Please get rid of the merged cells. They cause havoc for formulas in the simplest of workbooks. With everything you have going on here they will cause nightmares.

  7. #7
    Registered User
    Join Date
    06-12-2017
    Location
    Meridian, Idaho
    MS-Off Ver
    Excel - Version 1704 - MS Office 365
    Posts
    13

    Re: Using Data Validation to select one of five lists of names.

    I have removed the merged cells from the Roster tab, and added the label of the second column of each roster.
    I'll attach my current draft of the worksheet.
    If you look at the "Report 1" tab, you'll see three different data validation lists in C3:E3.
    C3 is showing the week columns from the Data tab,
    D3 and E3 currently show the same roster; but I'd like D3 to display the "Week 1 Roster, Week 2 Roster..." instead.
    i.e., I want it to choose which roster cell E3 displays w/ its list.
    Thanks again for the replies!
    Attached Files Attached Files

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,806

    Re: Using Data Validation to select one of five lists of names.

    You're welcome. This is probably going to take me some time to figure out ... of course there is always Divine Inspiration . We'll see.

  9. #9
    Registered User
    Join Date
    06-12-2017
    Location
    Meridian, Idaho
    MS-Off Ver
    Excel - Version 1704 - MS Office 365
    Posts
    13

    Re: Using Data Validation to select one of five lists of names.

    I really appreciate your time! And am hoping for that Divine Inspiration, too!
    I'm really trying to consolidate the worksheet, so that it's not so cumbersome.
    Thanks again,
    jaljr

  10. #10
    Registered User
    Join Date
    06-12-2017
    Location
    Meridian, Idaho
    MS-Off Ver
    Excel - Version 1704 - MS Office 365
    Posts
    13

    Re: Using Data Validation to select one of five lists of names.

    Hello again,
    I consolidated the Week tabs into one "Feedback" tab using Groups and color-coded headings. This has helped w/ the business, imo.
    All that's left now is figuring out how to consolidate the five Report tabs into one tab. A Report tab that can call any one of five rosters.
    Thanks again for any help.
    jaljr
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,410

    Re: Using Data Validation to select one of five lists of names.

    Not sure if this what is required:

    Tab "Report"

    in D7

    =IFERROR(INDEX(Data!$B$1:$F$17,MATCH($C7,Data!$A$1:$A$17,0),MATCH(Report!$C$3,Data!$B$2:$F$2,0)),"")

    copy down to D11

    similar formulae in D14/D18, C18

    Data changes on selection of Week
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-12-2017
    Location
    Meridian, Idaho
    MS-Off Ver
    Excel - Version 1704 - MS Office 365
    Posts
    13

    Re: Using Data Validation to select one of five lists of names.

    Hello JohnTopley,
    Thank you for the reply.
    With your solution, I still don't see that I can choose another week's roster from the Roster tab?
    It does great at showing the different week's data for the Week 1 roster; but doesn't allow me to switch to the Week 2 - Week 5 rosters.
    I was hoping this could be done with a data validation cell in D3; but I'm wondering:
    Could this possibly be done w/ a "smart" roster? One that calls one of the five rosters?
    What I'm thinking is: maybe having six rosters on the Roster tab? One of the rosters would be "smart". e.g., if the C3 cell on the Report tab shows Week 2, then the "smart" roster would show the information from D2:E30 in it, etc.?
    Thank you again for the reply.
    Sincerely,
    jaljr

  13. #13
    Registered User
    Join Date
    06-12-2017
    Location
    Meridian, Idaho
    MS-Off Ver
    Excel - Version 1704 - MS Office 365
    Posts
    13

    Re: Using Data Validation to select one of five lists of names.

    Maybe another way to do this would be to put multiple If/Then formulas in each cell of the "smart" roster?
    e.g., for cell A2 on the Roster tab (see attached), a formula that checks for the Week showing in cell C3 of the Report tab.
    If C3 shows "Week 2", then A2 would display "John Test2"; if C3 shows "Week 3", then A2 would display "John Test3", etc.?
    Here's an image of the Roster tab draft I'm attaching:
    Smart Roster.JPG
    Thanks again for any help,
    jaljr
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    06-12-2017
    Location
    Meridian, Idaho
    MS-Off Ver
    Excel - Version 1704 - MS Office 365
    Posts
    13

    Re: Using Data Validation to select one of five lists of names.

    Hello again,

    The "smart" roster did the trick!

    Here's the formula I put in A2 on the Roster tab:

    =IF(ISBLANK(IF(Report!$C$3=Data!$B$2,Roster!D2,IF(Report!$C$3=Data!$C$2,Roster!G2,IF(Report!$C$3=Data!$D$2,Roster!J2,IF(Report!$C$3=Data!$E$2,Roster!M2,IF(Report!$C$3=Data!$F$2,Roster!P2,"")))))),"",IF(Report!$C$3=Data!$B$2,Roster!D2,IF(Report!$C$3=Data!$C$2,Roster!G2,IF(Report!$C$3=Data!$D$2,Roster!J2,IF(Report!$C$3=Data!$E$2,Roster!M2,IF(Report!$C$3=Data!$F$2,Roster!P2,""))))))

    Here's the formula I put in B2:

    =IF(ISBLANK(IF(Report!$C$3=Data!$B$2,Roster!E2,IF(Report!$C$3=Data!$C$2,Roster!H2,IF(Report!$C$3=Data!$D$2,Roster!K2,IF(Report!$C$3=Data!$E$2,Roster!N2,IF(Report!$C$3=Data!$F$2,Roster!Q2,"")))))),"",IF(Report!$C$3=Data!$B$2,Roster!E2,IF(Report!$C$3=Data!$C$2,Roster!H2,IF(Report!$C$3=Data!$D$2,Roster!K2,IF(Report!$C$3=Data!$E$2,Roster!N2,IF(Report!$C$3=Data!$F$2,Roster!Q2,""))))))

    I was able to fill the cells below from there.

    I'm pretty sure this worksheet is about good to go!

    Thank you again for all the help!

    jaljr
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,410

    Re: Using Data Validation to select one of five lists of names.

    In "Roster"

    in A2

    =INDEX($D$2:$Q$30,ROWS($1:1),MATCH(Report!$C$3 & "*",$D$1:$Q$1,0))

    in B2

    =INDEX($D$2:$Q$30,ROWS($1:1),MATCH(Report!$C$3 & "*",$D$1:$Q$1,0)+1)

    Remove colon ":" from "Week 1:" etc in "Data"
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    06-12-2017
    Location
    Meridian, Idaho
    MS-Off Ver
    Excel - Version 1704 - MS Office 365
    Posts
    13

    Re: Using Data Validation to select one of five lists of names.

    Hello JohnTopley,
    Those formulas look a lot more streamlined than mine. I'll try them out.
    Thank you very much!
    jaljr

+ 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] Three Data Validation Lists: Third List works from first two Lists BUT...
    By em1335 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-07-2016, 12:46 AM
  2. Replies: 3
    Last Post: 06-04-2015, 02:27 PM
  3. [SOLVED] data validation lists - drop down lists too small
    By reglook0736 in forum Excel General
    Replies: 3
    Last Post: 04-24-2015, 12:49 PM
  4. Replies: 5
    Last Post: 03-26-2015, 01:40 PM
  5. Using Defined Names with Data Validation Depend and Data Validation Multi Select
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-01-2012, 05:36 PM
  6. Replies: 3
    Last Post: 09-02-2010, 03:04 AM
  7. Replies: 5
    Last Post: 05-21-2010, 04:34 PM

Tags for this Thread

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