Function Problems

Store and Use Prerequisite Lists in Excel Cells

Steven ParkerSteven Parker Nov 14, 2026 868 views

Question details

The user wants to store prerequisite values and use them for conditional formatting and dependent drop-down lists.

How to Store and Use Prerequisite Lists in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the source sheets

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.

2
Check each prerequisite

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.

3
Calculate ability eligibility

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.

4
Highlight eligible abilities

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.

5
Create the dependent drop-down

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.

Use helper tables to check prerequisites and build an eligible list
Check the source data: Ability names must match across sheets, and every prerequisite row must have its check formula filled in. If no abilities qualify, the FILTER formula returns a blank list item. Expand the referenced ranges if your data exceeds row 100.
Free Microsoft Office alternative

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.

Organize abilities and prerequisites in spreadsheet tablesCompatible with Microsoft Excel XLS and XLSX formatsFamiliar interface for working with formulas and formattingFree, lightweight alternative for everyday office tasks
microsoft office alternative - wps office

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.