Multiple dependent columns without defined list of values

[candace_tt]

Hi all. 

I have a spreadsheet of objects for a data migration exercise. 

Sample:

DepartmentModuleObject IDObject Name

CP	PM	EG1	Example 1
SCM	SCM	EG2	Example 2
Finance	AP	EG3	Example 3
CP	PPM	EG4	Example 4
SCM	Proc	EG5	Example 5
Finance	GL	EG6	Example 6
EAM	EAM	EG7	Example 7

For the purpose of tracking migration issues, I am to use this spreadsheet in such a way that whomever is tracking the issues will:

1. Select a Department from a drop down list (I did this using a list of values and then data validation referencing the list) 
2. Select a Module from a dependent drop down list based on what was selected in step 1 (I did this using data validation and the INDIRECT function)
3. Select an Object ID that is filtered based on what was selected at step 2 - this is where I am stuck! The object IDs are just listed in the existing spreadsheet, there is no definition of the relationship between department-module-object ID
4. Auto-populate the Object Name based on selection in step 3 (I am thinking I can use VLOOKUP for this). 

Any ideas on Excel or Power Query functionality that can be used to achieve this? 

Not sure if this is relevant but I am using M365 Apps. 

Thank you!

[#2 · Accepted solution]

@candace_tt 

You can achieve this by using a combination of data validation and the INDIRECT function.

Here’s how you can do it:

1. Create a named range for each module that contains the corresponding Object IDs. For example, create a named range called “PM” that contains all the Object IDs for the PM module, a named range called “SCM” that contains all the Object IDs for the SCM module, and so on.
2. In the cell where you want to create the drop-down list for Object IDs, use data validation with the INDIRECT function to create a dynamic list based on the selected module.

For example, if the selected module is in cell B2, you can use the following formula in the data validation source field: =INDIRECT(B2). This will create a drop-down list that displays the Object IDs for the selected module.

1. To auto-populate the Object Name based on the selected Object ID, you can use a VLOOKUP or INDEX/MATCH formula. For example, if the selected Object ID is in cell C2 and your data table is in cells A2:D8, you can use the following VLOOKUP formula to return the corresponding Object Name: =VLOOKUP(C2,A2:D8,4,FALSE).

I hope this helps! 

NikolinoDE

I know I don't know anything (Socrates)

[#3]

Thanks so much for taking the time to help with this! Apologies for the delayed reply.