Forum Discussion
Multiple dependent columns without defined list of values
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:
- Select a Department from a drop down list (I did this using a list of values and then data validation referencing the list)
- 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)
- 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
- 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!
You can achieve this by using a combination of data validation and the INDIRECT function.
Here’s how you can do it:
- 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.
- 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.
- 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!
I know I don't know anything (Socrates)
2 Replies
You can achieve this by using a combination of data validation and the INDIRECT function.
Here’s how you can do it:
- 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.
- 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.
- 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!
I know I don't know anything (Socrates)
- Thanks so much for taking the time to help with this! Apologies for the delayed reply.
