Function Problems

WPS Spreadsheet: Automatically Populate Course Attendees by Date

Huma Ashraf Ch Nov 12, 2026 868 views

Question details

The user needs a training schedule that automatically displays employees under the correct course and date.

Automatically Populate Course Attendees by Date in WPS Spreadsheet

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: Course names are listed down the left side of a worksheet, dates run across the top, and an attendee list contains employee names, course names, and course dates.
Observed behavior: The workbook does not yet automatically return the matching employees for each course-date intersection.

Before you start

Prepare a source table with one attendee per row and separate columns for Employee, Course, and Date. Ensure the schedule dates and source dates are stored as real spreadsheet dates rather than text.

Solution 1Recommended

Use a FILTER and TEXTJOIN formula to populate each course-date cell

Use this method when your WPS Spreadsheet version supports dynamic array functions and you want the schedule to update automatically as attendee records change.

The formula compares the course name in the current row with the date in the current column, filters matching employee names, and combines them into one cell. Replace the sheet name and ranges with the locations in your workbook.

Prepare the attendee table

On an Attendees sheet, place employee names in column A, course names in column B, and course dates in column C. Use headers such as Employee, Course, and Date in row 1.

Set up the schedule headings

On a Schedule sheet, enter course names in A2:A100 and actual course dates in B1:Z1. Format the date headings with Home > Number Format > Date if needed.

Enter the matching formula

Select Schedule!B2 and enter =IFERROR(TEXTJOIN(", ",TRUE,FILTER(Attendees.$A$2:$A$1000,(Attendees.$B$2:$B$1000=$A2)*(Attendees.$C$2:$C$1000=B$1))),""). Press Enter to return all matching employees.

Fill the schedule

Drag the fill handle from B2 across the date columns and down the course rows. Each cell will display the employees whose course and date match that row and column.

Keep dates consistent: If a matching cell is blank, compare the source and schedule dates using Home > Number Format or Format Cells. Both values must represent the same date, including any hidden time value.

Solution 2

Use a WPS Spreadsheet macro for older versions or custom attendee logic

Use a macro when FILTER is unavailable, when the attendee data needs additional conditions, or when you want to rebuild the entire schedule with one command.

Create an Automatically Updating Training Schedule in WPS Spreadsheet

WPS Spreadsheet can organize attendee records, match course names and real date values, and populate a cross-tab training schedule with formulas. Its familiar spreadsheet interface makes it easy to edit the source list and refresh the schedule without rebuilding the workbook.

1. Create the source list: Open WPS Spreadsheet and create columns for Employee, Course, and Date on an Attendees sheet.
2. Build the schedule grid: Add course names down column A and course dates across row 1 on a Schedule sheet.
3. Insert the matching formula: Select the first schedule intersection and use a FILTER plus TEXTJOIN formula to return employees matching the row course and column date.
4. Copy and verify: Fill the formula across and down, then check that source dates and schedule headings use the same date values.

Automatically match employees to the correct course and dateUse formulas to keep the attendee schedule updated as records changeOrganize source data and schedule headings in a familiar spreadsheet interfaceOpen and save Microsoft Excel XLSX files with strong format compatibility

microsoft office alternative - wps office

Frequently Asked Questions

What information is required to populate attendees automatically?

You need at least three fields for each record: employee name, course name, and course date. The schedule also needs course names in one direction and dates in the other.

Why does the formula return a blank cell even when an attendee appears to match?

The course name or date may contain extra spaces, different spelling, text-formatted dates, or hidden time values. Check the source and schedule cells with Format Cells and standardize the values.

Can multiple employees appear in one course-date cell?

Yes. TEXTJOIN combines all names returned by FILTER into one cell, separated by commas. You can change the separator in the formula to CHAR(10) for line breaks and enable Wrap Text from the Home tab.

Can I use this schedule in Microsoft Excel after creating it in WPS Spreadsheet?

Yes. Save the workbook through Menu > Save As and choose Excel Workbook (*.xlsx). The layout and commonly supported formulas are designed for XLSX compatibility, although formula availability can vary by software version.

Excel & Data Problems