Data Validation & Drop-Down Lists

[crashing1912]

Hi All,

I have a drop-down data validation question. I have 5 columns that users are required to fill out when adding a new row. I'd like the help the users by adding drop-boxes that will narrow the choices down for them and reduce inaccurate mapping. I've figured out how to have dependent lists, which would work if the user always started in column A. However, the user may only know the Department Name and need to work backwards filling in columns E-A.

Here's an example set.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]BU[/TD]
[TD]OU #[/TD]
[TD]OU Name[/TD]
[TD]Dept #[/TD]
[TD]Dept Name[/TD]
[/TR]
[TR]
[TD]20000[/TD]
[TD]20750[/TD]
[TD]Digital [/TD]
[TD]920[/TD]
[TD]Analytics[/TD]
[/TR]
[TR]
[TD]20000[/TD]
[TD]20625[/TD]
[TD]MSU[/TD]
[TD]924[/TD]
[TD]Finance[/TD]
[/TR]
[TR]
[TD]23000[/TD]
[TD]20200[/TD]
[TD]LM[/TD]
[TD]900[/TD]
[TD]Human Resources[/TD]
[/TR]
</tbody>[/TABLE]


Thanks!

 

[easy2understandexcel]

To give you two ideas:
1. you could use VBA code to set the value that the dropdown lists are filtered by to the active cell's row of values that are already entered.
OR
2. you could make the dynamic lists source stored within each row horizontally.