Data Validation List based on Criteria

Evening,

I'm becoming stuck on trying to work out how to create data validation lists.

What I'm trying to achieve is a data validation based on criteria.

So there will be 4 possibly 5 columns and a user will select from the first data list in column 1, which is Director.
The next column, which is General Manager the user will then be able to pick from a data list that will have the direct reports to the Director selected in the previous column.
The next column, which is Business Manager the user will then be able to pick from a data list that will have the direct reports to the General Manager in the previous column.
The next column, which is Team Leader the user will then be able to pick from a data list that will have the direct reports to the Business Manager in the previous column.

I have a massive data set to work with hundreds of names, is this something that would be easily achievable in excel or should I look at doing it another way?


Thank you.

Thanks for these links.

VBA isn't something I'm familiar with sadly - even though the second link would work a treat I'm not sure I understand how it's all working or if it was to break how I could ever fix it 🤣

Hi,

I was sent a great link as a solution to my cascading list query that I had and looking at the information and watching the YT video it's exactly what I need. I don't want the combo boxes but more the data validation input part.

The link is https://www.thesmallman.com/blog/202...-3-and-4-layer

I'm struggling with the actual VBA code as it's not an area I'm familiar with at all.

The example on the link is based on 3 columns of data and a 4th column of numbers is added without changing anything from a VBA point.

If I had 6 columns of data is there something in the code that I need to change to expand the range?

I've attached a copy of the code from the attached link.

Thanks!

Originally Posted by AliGW

Threads merged and moved to the VBA section.

Thank you for doing this. I thought it was a separate ask so I appreciate that you have merged the posts together.

Many thanks!

Originally Posted by alansidman

Look at these two links for possible methods

https://www.contextures.com/exceluse...omboboxes.html

https://www.thesmallman.com/blog/202...-3-and-4-layer

Thanks for sharing these links - bottom one is exactly what I'm looking for. Just need the knowledge to make it work

Here's another option:
This is an example of 3 dependent data validation, with VBA. You can easily set up more than 3 dependent data validations as needed.
Notes:
1. You only need 1 table as data validation source, 1 helper column, 1 named range & 1 simple formula in data validation (ie =xName).
2. The columns where data validation reside may or may not be contiguous.
3. The list in the table may have duplicate, empty & unsorted, but the code will make the list in data validation unique, sorted & non-empty. The list is also dynamic, you can add more data as needed.
4. In the range with data validation, changing or deleting cell contents will delete cells in the next column with data validation.
5. But one caveat of using macro is when macro changes/writes something on sheet it will delete Undo Stack, so at that time you can't use UNDO. In this case it happens every time you put the cursor in a cell with data validation.

The workbook:

Originally Posted by Akuini

Here's another option:
This is an example of 3 dependent data validation, with VBA. You can easily set up more than 3 dependent data validations as needed.
Notes:
1. You only need 1 table as data validation source, 1 helper column, 1 named range & 1 simple formula in data validation (ie =xName).
2. The columns where data validation reside may or may not be contiguous.
3. The list in the table may have duplicate, empty & unsorted, but the code will make the list in data validation unique, sorted & non-empty. The list is also dynamic, you can add more data as needed.
4. In the range with data validation, changing or deleting cell contents will delete cells in the next column with data validation.
5. But one caveat of using macro is when macro changes/writes something on sheet it will delete Undo Stack, so at that time you can't use UNDO. In this case it happens every time you put the cursor in a cell with data validation.

The workbook:

This looks great - I will have a look more into this solution. Thank you for this, very useful indeed!

Originally Posted by CheeseSandwich

Attached is another method using the FILTER function to look up the latest 'DIRECTOR' to be added to the list in column A. The downside to this method is that the validation lists will always be set up for the latest entry in column A so if you wanted to amend the dropdown of the director above the one you just selected then the lists would be wrong.

On the 'Staff List' sheet:
H1: =INDEX(A32:A48,COUNTA(A32:A100))
I2: =FILTER(B3:B27,A3:A27=H1)
J2: =FILTER(C3:C27,A3:A27=H1)
K2: =FILTER(D3:D27,A3:A27=H1)

Director valdation formula: =$A$3:$A$27
General Manager valdation formula: =INDIRECT("I2:I" & COUNTA(I:I))
Business Manager valdation formula: =INDIRECT("J2:J" & COUNTA(J:J))
Team Leader valdation formula: =INDIRECT("K2:K" & COUNTA(K:K))

File attached for reference.

Thanks for this suggestion. Another possible solution that I will look into and see if I can use this. Thanks again!

Thank you all for the help - managed to sort something out.

I'll close this thread.

Originally Posted by Akuini

Here's another option:
This is an example of 3 dependent data validation, with VBA. You can easily set up more than 3 dependent data validations as needed.
Notes:
1. You only need 1 table as data validation source, 1 helper column, 1 named range & 1 simple formula in data validation (ie =xName).
2. The columns where data validation reside may or may not be contiguous.
3. The list in the table may have duplicate, empty & unsorted, but the code will make the list in data validation unique, sorted & non-empty. The list is also dynamic, you can add more data as needed.
4. In the range with data validation, changing or deleting cell contents will delete cells in the next column with data validation.
5. But one caveat of using macro is when macro changes/writes something on sheet it will delete Undo Stack, so at that time you can't use UNDO. In this case it happens every time you put the cursor in a cell with data validation.

The workbook:

Hi Akuini- Just wanted to come back and feedback that following further changes to the excel project I was working on which needed data validation lists this solution as posted by you worked perfectly. Solution was very easy to follow and also easy to incorporate into my build. The excel project has now successfully completed. Thanks again!

Originally Posted by Miggy2021

Hi Akuini- Just wanted to come back and feedback that following further changes to the excel project I was working on which needed data validation lists this solution as posted by you worked perfectly. Solution was very easy to follow and also easy to incorporate into my build. The excel project has now successfully completed. Thanks again!

You're welcome, glad to help & thanks for the feedback.