Store Date-Specific Yes, No, and N/A Responses in Excel
Question details
Create a worksheet structure that stores different Yes, No, or N/A responses for each date while preserving the original information in columns A and B.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to record responses for the same items on multiple selected dates and view the records for a particular date.
- Observed behavior
- Using the same response cells for each date would overwrite prior Yes, No, and N/A entries; responses need to remain associated with their individual dates.
Keep columns A and B as the stable item information, such as an ID and description. Add a separate Date field for every response record so previous date entries are not replaced.
Create a date-based response table with a Yes, No, and N/A drop-down list
Use this recommended structure when each item can have a different response on many dates and you need to keep a complete response history.
A normalized response table avoids overwriting values in columns D:G. Each row represents one item response on one date, while columns A and B can continue to hold the unchanged item details.
For example, use columns such as Item ID, Item Name, Date, Response 1, Response 2, Response 3, and Response 4. Enter a new row when the same item is reviewed on another date.
In the response table, insert a Date column before the columns that store the Yes, No, and N/A responses. Enter the relevant review date on each response row rather than replacing an earlier row's values.
On an unused area of the worksheet, enter Yes, No, and N/A in three separate cells, for example J2:J4. Select the response cells in columns D:G, then choose Data > Data Validation.
In the Data Validation dialog, choose List or List from the Allow menu, then set the Source to =$J$2:$J$4. Select OK so each response cell has a Yes, No, and N/A drop-down.
For each selected date, add a new response row with the item ID, item details or lookup result, date, and values in columns D:G. Do not overwrite the earlier date's row.
Display all responses for a selected date with FILTER
Use this option when you want a separate viewing area that automatically shows only the records for a date selected in one cell.
Track Date-Specific Yes, No, and N/A Responses in WPS Spreadsheets
WPS Spreadsheets lets you build a date-based response table, add Yes/No/N/A drop-down controls, and use FILTER formulas to display records for a selected date. This keeps your item information intact while maintaining a clear history for every review date.
- 1. Set up response columns: Open the workbook in WPS Spreadsheets and add a Date column alongside the item fields and response columns.
- 2. Create the response options: Enter Yes, No, and N/A in helper cells, then select the response range and choose Data > Data Validation > List.
- 3. Save one record per date: Add a new row for each item and review date, then select the appropriate value from each response drop-down.
- 4. Build a selected-date report: Enter a date in a report selector cell and use the FILTER function to return the matching response rows.
Frequently Asked Questions
Why should I not reuse the same Yes, No, and N/A cells for each date?
Reusing the same cells replaces the earlier values. Add a Date column and store a new row for each date so every response remains available for reporting.
How do I add a Yes, No, and N/A drop-down list in Excel?
Enter Yes, No, and N/A in three cells, select the response cells, then go to Data > Data Validation. Choose List and set the Source to the three-cell range.
Can I show all records for one selected date automatically?
Yes. Put the target date in a selector cell and use a formula such as =FILTER(A2:G100,C2:C100=L2,"No records for this date"), adjusting the ranges so column C is your Date column.
Should columns A and B be copied for every response date?
They can be repeated in each response row for a simple table, or you can store item details once in a master list and use XLOOKUP to return those details from an item ID. In either design, the response row must include its own date.
Can I use this date-based response layout in WPS Spreadsheets?
Yes. WPS Spreadsheets supports Data Validation lists and spreadsheet formulas such as FILTER, and it can work with Excel .xlsx workbooks.
