Results 1 to 2 of 2

Thread: DATA VALIDATION with INDEX MATCH array, possible?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2018
    Location
    North Carolina
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question DATA VALIDATION with INDEX MATCH array, possible?

    Hello all,

    Context:
    I am working on a spreadsheet for our PMO to automatically enter data from a CSV file into a large table of agent working hours, then calculate the project's budget in a Pivot Table for their reports. They have been very thrilled with the results, however, there is one issue that we came across in design that I haven't been able to solve.

    The data entry section moves the information entered over to a calculations table which matches the agent's information up with the team they work for (from a team information table). This is important for separating out team calculations within the pivot table. However, the problem is that a select few agents perform work for multiple teams, depending on what they're needed for, and the PMO needs this to be reflected in the budget.

    Question:
    I've added a field to the data entry table, which allows the PM to select the team agents work for from a drop-down list, derived from the team table. However, I would like for it to only show the teams the agent is associated within the drop-down list (data validation), so that they can't select an invalid team for the agents, and so that the list will be shorter.

    Is it possible, through formula or VBA, to set up a variable array that the data validation list uses, based on the agent's name in column D, so that only the teams they are assigned to will show up in the drop-down list next to their name?

    Table5: (Formatted as a table, for data entry and pivot table purposes)

    Email Time Worked Matched Name Team
    John.Smith@Contoso.com 120 John Smith RPG
    John.Smith@Contoso.com 300 John Smith Java

    ^ So I would like for fields in the column named Team to have a data validation drop-down list in them which reflects the teams (from table 3, below) that John Smith is listed for (RPG and Java). ****

    Table3: (formatted as a table, for data entry and pivot table purposes)
    Team Agent Email Rate
    RPG John Smith John.Smith@Contoso.com $60.00
    Java John Smith John.Smith@Contoso.com $60.00
    Operations Jane Doe Jane.Doe@Contoso.com $60.00
    SAP Jill Smith Jill.Smith@contoso.com $60.00

    Thank you, in advance!

    Sincerely,
    Kristopher

  2. #2
    Board Regular Rijnsent's Avatar
    Join Date
    Oct 2005
    Location
    Utrecht, Holland
    Posts
    1,046
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DATA VALIDATION with INDEX MATCH array, possible?

    Hi Kristopher,
    that looks like a good challenge. I think this tutorial explains what you're looking for: https://www.excel-university.com/cre...ta-validation/
    Cheers,
    Koen
    You can't post attachments here, but you can help me helping you by posting a screen shot directly in your post with any of those tools.
    Otherwise use dropbox/google drive/etc to get your file accross (not preferred). For code, put it inside these tags: [ CODE][/CODE]. Do check the forum rules.
    Finally, please show that you made an effort to solve your problem: Yes, I like to help, but am not going to do your job.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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