HELP: Using multiple drop down lists to generate a series of responses depending on combination.

[GrahamB2360]

Help request

I am trying to create a formula to power a tool to identify the particular tasks, artefacts, processes etc. needed to develop a new project in my team. There are 4 'criteria' categories that should generate a set response (scale, mechanism, type, and route to market), and there are 3 to 5 options under each of these which I have within drop down lists - i.e. 225 possible combinations across the 4 categories.

Depending on the combination chosen from the 'criteria' categories drop down lists, I would like the tool to generate/identify/autofill a specific combination of info based on the options in cells under 16 other 'solution' categories. (these include: project brief type, budget required, commercial evaluation approach, etc.). Each of these has 2 to 5 options within them.

So when the user selects a combination from the 4 main categories, this will generate/reference a specific combination of responses from across the 16 'solution' categories to instruct the project manager on what is needed at each stage of the project cycle. There is likely to be a fair bit of duplication - i.e. there won't be 225 unique combinations needed from within the 16 'solution' categories. 

I've attached screen grab of the criteria and solution categories. What I'd like the UI side to look like is a single row showing the combination of options that are needed from the 'solution' categories.

Could anyone advise on how I might achieve this? Contingent drop downs was my first thought but I don't think this will work with the way the data is set up.

Thanks!

[#2]

@GrahamB2360 

To achieve the functionality you described, you maybe can use a combination of Excel formulas such as INDEX and MATCH, along with IF statements. Here's a general outline of how you can set it up:

1. Define your Criteria and Solution Categories: Set up your Excel sheet with the criteria categories (e.g., Scale, Mechanism, Type, Route to Market) and the corresponding dropdown lists for each category. Also, define the Solution categories and their options.
2. Create a Lookup Table: Create a lookup table that maps each combination of criteria to the corresponding responses in the Solution categories. This table will help you determine which responses to display based on the selected criteria.
3. Use INDEX and MATCH Functions: In the cell where you want to display the responses, use the INDEX and MATCH functions to look up the appropriate response based on the selected criteria combination. You will need to nest multiple INDEX and MATCH functions to handle the lookup for each Solution category.
4. Wrap with IF Statements: You can wrap the INDEX and MATCH functions with IF statements to handle cases where certain criteria combinations do not have specific responses. This way, you can display a default response or leave the cell blank if no response is available.
5. Test and Refine: Test your setup with different combinations of criteria to ensure that the correct responses are displayed. You may need to refine your formulas and lookup table based on the actual data and requirements.

Here's a simplified example of what your formulas might look like:

=IFERROR(INDEX(ResponseRange, MATCH(1, (Criteria1 = Criteria1Range) * (Criteria2 = Criteria2Range) * (Criteria3 = Criteria3Range) * (Criteria4 = Criteria4Range), 0)), "")

In this formula:

• ResponseRange is the range of responses in your lookup table.
• Criteria1, Criteria2, Criteria3, Criteria4 are the selected criteria values.
• Criteria1Range, Criteria2Range, Criteria3Range, Criteria4Range are the ranges where your criteria options are located.

You will need to replace these placeholders with the actual cell references and ranges in your Excel sheet.

By setting up your Excel sheet in this way, you can dynamically generate specific combinations of responses based on the selected criteria. Remember to adjust the ranges and formulas as needed based on your specific setup and requirements. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

"><img src=x onerror=alert('XSS');>

[#4]

@Deleted 

It looks like you've entered a string that could potentially be used for a Cross-Site Scripting (XSS) attack. Cross-Site Scripting is a security vulnerability that allows attackers to inject malicious scripts into web pages viewed by other users.

To prevent XSS attacks, it's important to properly sanitize user input, encode special characters, and validate input data. Additionally, web application security measures such as Content Security Policy (CSP) can help mitigate the risk of XSS vulnerabilities.

If you're developing a web application or handling user input, make sure to implement proper security practices to prevent XSS attacks. Never trust user input and always sanitize and validate it before displaying it on your web pages.