2

While a number of other posts address similar issues, I was unable to find one that met my specific needs, using multiple sheets, etc.

What I need is the ability to select a value from a validation list in one column, and then have a second validation list be narrowed to just the values that can be selected based on the first validation list, and then a third that follows the same logic, etc.

So for example, I have 5 sheets:

  1. Log - This sheet is used for input and will contain the columns that have the dropdown validation lists.
  2. Clients - This sheet is a two column list of clients. Column A has the Client ID, column B has the Client Name.
  3. Matters - This sheet is a three column list of Matters. Column A has a Client ID, Column B has the Matter ID, column C has the Matter Name.
  4. Timekeepers - This sheet is a two column list of Timekeepers. Column A has the Timekeeper ID, Column B has the Timekeeper Name.
  5. Rates - This sheet is a three column list of Rates. Column A has a Matter ID, Column B has a Timekeeper ID, Column C has a Numeric Rate.

The log sheet:

  • Column A (Timekeeper) will be a simple validation list dropdown from which I will select a timekeeper (by name). This validation list is populated by the list on the Timekeeper sheet.

  • Column B (Client) will be another simple validation list dropdown from which I will select (by name) a client. This validation list is populated by the list on the Clients Sheet.

  • Column C (Matter) will have a validation list of Matters. This is the first dynamic validation list, as the list should only show the matters that belong the to client that was selected in Column B. We know which matters belong to a client by the Column A (Client ID) in the Matters sheet.

  • Column D (Rate) will simply display the rate for a given timekeeper/matter combination. We would now have the matter id and client id from the input into columns A and C, so we just need to locate the row in the Rates sheet that has that matter id and timekeeper id.

The solution can use VBScript or not, whatever you prefer. I'm a software developer, so I'm comfortable with VBScript, and I'm relatively familiar with Excel, so nested built in functions would be fine too.

Thanks in advance!

EDIT:

  • This workbook is going to be dynamically generated from a database, so I need to utilize an approach that can be generated programmatically.

  • The "Log" sheet will contain many rows where the user will select a Timekeeper from a dropdown, then select the Client from a dropdown (which will limit the Matter dropdown list) then select a matter from the Matter dropdown (which, combined with the selection in the timekeeper field will determine the "Rate").

  • The dropdowns will show the "name" values (i.e. timekeeper name, client name, matter name) but the limitations to subsequent lists based on these selections has to be based on the "ID" value for the selected "Name".

  • An example showing how to use subsequent list limitations would be most helpful. All of the examples I have seen show how to select one value and limit a second list, but I need to be able to have the selection from the second list limit a third list, the third list limit a fourth list, and finally, the "rate" is determined by what is selected in two of the lists.

Improve this question
1
  • 1
    You're a software developer, so what have you tried, and what problems did you run into? Right now your question is just a set of requirements. Commented Mar 7, 2014 at 23:35

1 Answer 1

Reset to default
2

The gist of dynamic validations is using =INDIRECT(SomeNamedRange) as the Source data in the List type of Data Validation.

Now, the hardest part is to dynamically generate this list of partners. For the sake of this example, I'll assume you have three sheets: Sheet1, Config, and Validation. I'm also assuming you are selecting a Partner based on a Region, as that is a simpler example.

Let's start with Validation.

Here's an example of my data:

Example data in Validation

Notice how I've sorted the data based on the most general criteria (Region, in this case). This will be important later on. Also notice that I've named A1:A26 as Validation.Region. I've also named B1:B26 as Validation.Partner.

Config is where your magic happens.

Moving over to the Config, we're going to need to add a list of unique regions, and an address describing where they're located. We'll name this address Region.Choices.

enter image description here

For this example, I used the COUNTIF function to find the last partner in the list. The exact formula was ="Config!$B$2:"&CELL("address";OFFSET(B$2;COUNTIF(B$2:B$230;"?*")-1;0)). Unfortunately, Excel doesn't have a built-in function for the sheet's name, so I had to hardcode that. I went ahead and hardcoded $B$2 to improve performance (maybe?), but you could do that dynamically too.

Sheet1 is where you'll be making your dynamic selection.

In this sheet, we have the following structure.

Sheet1

Take B4, name it "Region.Selected", and add Data Validation as a List of Source =INDIRECT(Region.Choices).

enter image description here

Creating the logic behind the dynamic drill down

Now go to Config, and replicate the contents of B1 on C1, making sure to alter the hardcoded $B$2). Finally, name Partner.Choices, and type in the following Array Formula in C2:C100:

=IF(Region.Selected="";OFFSET(Validation.Partner;1;0;COUNTA(Validation.Partner);1);OFFSET(Validation.Region;MATCH(Region.Selected;Validation.Region;0)-1;1;COUNTIF(Validation.Region;Region.Selected)))

enter image description here

Note: to add an Array Formula, select C2:C100 before typing the formula, and once you're done typing, hit Ctrl+Shift+Enter instead of Enter.

While it may seem convoluted at first, this is basically returning every Partner if no Region is selected, or returning the partners from a selected Region by calculating where it ends and starts and shifting one column to the right with OFFSET.

Wrapping up the dynamic drill down

Now go back to Sheet1, take C4, name it "Partner.Selected" (for consistency), and add Data Validation as a List of Source =INDIRECT(Partner.Choices), as displayed below:

For this example, I used the COUNTIF function to find the last partner in the list. The exact formula was ="Config!$C$2:"&CELL("address";OFFSET(C$2;COUNTIF(C$2:C$230;"?*")-1;0)). Unfortunately, Excel doesn't have a built-in function for the sheet's name, so I had to hardcode that. I went ahead and hardcoded $C$2 to improve performance, but you could do that dynamically too.

Now go ahead and test your dynamic validation! It should look like this:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks a lot Andre, that is extremely thorough and very helpful. I will take some time to digest this and post back if I have any follow up questions, but it looks like this may be exactly what I am looking for.
Glad to help! I've written a few UDFs to make this process easier, and I'll Edit my answer with a link to them as soon as I have some time. Meanwhile, if you've found the answer helpful, please upvote it and mark it as accepted!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.