Dynamic Drop Down List based on a different cell value

Hello,
I am try to create a drop down lists when a cell have been selected, it only shows the possible users in that specific account number

*All the data in these sheet are made up, real data will consists of larger volume of data

For example, in the main sheet first row, user column
When selected, or when the dropdown is selected, it will only show the users in Account 101
For example, in Account Number 101 , LogID 123, User column will show a dropdown list of Bob, Bill, Derrick (from the users worksheet)

Sheet 1: Main
| Account Number | LogID | User |
|--------------------|-------|--------|
| 101 | 123 | |
| 102 | 124 | |
| 103 | 125 | |
| 102 | 126 | |
| 102 | 127 | |
| 103 | 128 | |
| 102 | 129 | |
| 103 | 130 | |
| 101 | 131 | |

Sheet 2: Users
| Account Number | User Name |
|----------------|-----------|
| 101 | Bob |
| 101 | Bill |
| 102 | Joey |
| 103 | John |
| 101 | Derrick |
| 102 | Shaun |
| 103 | Levi |
| 102 | Kevin |
| 103 | Zach |

Hi and welcome

If using VBA is an option for you then here's one method.

The below code goes in the worksheet module for the "Main" sheet.

Please Login or Register  to view this content.

This will stamp the Account Number for the selected row to D1 on the "Users" sheet and the below formula in E1 will generate a list of User Names for that Account Number.

Formula:

Please Login or Register  to view this content.

Then the below formula in a named range which you can use for your data validation.

Formula:

Please Login or Register  to view this content.

Give it a try in the attached where I've set all the above up.

BSB

I was trying to avoid VBA so other users can edit it through the web version but this might be the only way, might just ask them to use the desktop version. I wish filter() worked with the data validation source but we can't have everything can we?

Thank you for the help!

Another option would be to create lists for each of the Account Numbers and then use INDIRECT to dictate which list is used in a given row, but I was presuming your data would be somewhat dynamic and therefore that wouldn't be a viable option.

It is indeed a shame that FILTER won't work in Data Validation. Perhaps drop Billy Gates a line and demand he gets it added :D

BSB