Create an Excel Waitlist from Yes or No Data Validation
Question details
Create a waitlist sheet that displays records from an Overview sheet only when a Yes or No data-validation field is set to Yes.
- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- The user maintains source records on an Overview worksheet and wants a separate, automatically updated waitlist.
- Observed behavior
- Copying formula cells transfers displayed values or formulas but does not copy the source worksheet's data-validation rules to the destination.
Confirm that the Yes or No status is stored in a consistent column, such as column E on the Overview sheet. If you use FILTER, make sure the return range and the criteria range begin and end on matching rows.
Use the FILTER function to create a dynamic waitlist
Use this option in Microsoft 365 when you want the Waitlist sheet to automatically list only the Overview records marked Yes.
FILTER returns matching records as a dynamic array, so the list expands or contracts when the Yes or No status changes. Converting the source range to an Excel table can make the formula easier to maintain.
On the Overview sheet, place the Yes or No data-validation field in one consistent column, for example column E, and keep the records to display in columns A through B.
On the Waitlist sheet, select cell A1 and enter =FILTER(Overview!A:B,Overview!E:E="Yes","No entries"). Press Enter to spill all matching rows into the sheet.
If whole-column references slow the workbook, use matching limited ranges, such as =FILTER(Overview!A2:B500,Overview!E2:E500="Yes","No entries").
If users must select Yes or No on the Waitlist sheet, select the destination cells and choose Data > Data Validation, then create a List rule with Yes and No. The FILTER output itself does not transfer validation rules from Overview.
Use an IF formula and filter out blank rows
Use this method when FILTER is unavailable or when you prefer a row-by-row formula that can be filled across and down.
Filter and load waitlist records with Power Query
Use Power Query for a refreshable waitlist when the Overview data is maintained as a table and you want to load filtered results without worksheet formulas.
Build a Yes-Based Waitlist with WPS Spreadsheets
WPS Spreadsheets lets you create a live waitlist from Overview records marked Yes. Use a FILTER formula for an automatically expanding list, or use IF formulas and filters when a row-by-row layout is preferred.
- 1. Set up the Yes or No status column: In WPS Spreadsheets, select the status cells on the Overview sheet and use Data > Data Validation to create a list containing Yes and No.
- 2. Select the first Waitlist cell: Open the Waitlist sheet and select the cell where the matching records should begin, such as A1.
- 3. Enter a FILTER formula: Type =FILTER(Overview!A:B,Overview!E:E="Yes","No entries") and press Enter to show the records whose status is Yes.
- 4. Use a fallback formula when needed: For a row-by-row list, enter =IF(Overview!$E1="Yes",Overview!A1,"") and fill the formula across and down.
Frequently Asked Questions
Why does copying a formula not copy the Yes or No drop-down list?
A formula copies or calculates displayed values; it does not transfer the source cell's Data Validation rule. Select the destination input cells and create a separate rule through Data > Data Validation.
How do I show more than two columns on the waitlist?
Expand the FILTER return range. For example, use =FILTER(Overview!A:D,Overview!E:E="Yes","No entries") to return columns A through D when column E is Yes.
What happens when a status changes from No to Yes?
With a FILTER formula, the matching record appears in the spilled waitlist automatically. With Power Query, use Data > Refresh All to update the loaded table.
Can I use FILTER if the Overview data is an Excel table?
Yes. After naming the table, you can use structured references, such as =FILTER(Table1[[Name]:[Email]],Table1[Waitlist Status]="Yes","No entries"), using your actual table and column names.
