SOLVED

Multiple dependent columns without defined list of values

Copper Contributor

Hi all. 

 

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

Sample:

DepartmentModuleObject IDObject Name

CPPMEG1Example 1
SCMSCMEG2Example 2
FinanceAPEG3Example 3
CPPPMEG4Example 4
SCMProcEG5Example 5
FinanceGLEG6Example 6
EAMEAMEG7Example 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 Replies
best response confirmed by HansVogelaar (MVP)
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)

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

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
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)

View solution in original post