Store and Use Prerequisite Lists in Excel Cells
Question details
The user wants to store prerequisite values and use them for conditional formatting and dependent drop-down lists.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A spreadsheet tracks class prerequisites or game abilities with level, strength, and prerequisite requirements.
- Observed behavior
- The desired result is a drop-down that shows only options for which the user meets all requirements, with formatting that identifies eligible entries.
List the abilities, their level and strength requirements, and each prerequisite separately. The drop-down method below requires an Excel version that supports FILTER and spilled ranges.
Use helper tables to check prerequisites and build an eligible list
Use this approach when each ability can have multiple prerequisites and you want an automatically filtered drop-down.
Keep prerequisite relationships in separate rows rather than storing several values in one cell. This avoids a large dependency matrix and makes each requirement easier to check.
The example assumes every listed prerequisite is mandatory. Adapt the formulas if an ability has alternative prerequisites or additional class restrictions.
Create sheets named Abilities, Prereqs, Owned, and Sheet1. On Abilities, put ability names in A2:A100, required levels in B2:B100, and required strength scores in C2:C100; use 0 where no level or strength minimum applies. On Prereqs, put the ability name in column A and one required ability per row in column B. List owned abilities in Owned!A2:A100.
In Prereqs!C2, enter =COUNTIF(Owned!$A$2:$A$100,B2)>0 and fill the formula down for all prerequisite rows. TRUE means the required ability appears on the Owned sheet.
Enter the current level in Sheet1!B1 and strength score in Sheet1!B2. In Abilities!D2, enter =AND(B2<=Sheet1!$B$1,C2<=Sheet1!$B$2,COUNTIFS(Prereqs!$A$2:$A$100,A2,Prereqs!$C$2:$C$100,FALSE)=0) and fill it down through D100. An ability with no matching prerequisite rows passes the prerequisite check.
Select Abilities!A2:D100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =$D2=TRUE, choose a format, and confirm the rule.
In Sheet1!F2, enter =FILTER(Abilities!$A$2:$A$100,(Abilities!$A$2:$A$100<>"")*(Abilities!$D$2:$D$100=TRUE),""). Select the destination cell on Sheet1, choose Data > Data Validation, set Allow to List, and enter =$F$2# as the source.

Keep a readable list in one cell while using a separate prerequisite table
Use a single-cell list for display, but retain separate prerequisite rows for calculations and validation.
Work with prerequisite spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks and continue working with prerequisite tables as you migrate; check advanced formulas and validation behavior in your version.

Frequently Asked Questions
Can Excel treat a comma-separated cell as several data-validation choices?
A cell containing comma-separated text is generally treated as one cell value when referenced as a list source. Put choices in separate cells and use Data > Data Validation > List to reference that range.
How do I require more than one prerequisite for an ability?
Add one row per prerequisite on the Prereqs sheet. The COUNTIFS eligibility check passes only when none of that ability's prerequisite rows is marked FALSE.
What if my version of Excel does not support FILTER?
Use a helper range populated by another formula or process supported by your Excel version, then select that range under Data > Data Validation > List. Complex automatic filtering may require VBA.
How can I highlight abilities that a player qualifies for?
Calculate eligibility in a helper column, select the ability rows, and use Home > Conditional Formatting > New Rule with a formula such as =$D2=TRUE.




