Excel: Automatically Fill Recipient Sheets from Selected Master Table Rows
Question details
Create individual recipient or mailer worksheets that automatically display rows from a master table based on each worksheet's name.
- Product
- Microsoft Excel workbook with a structured table
- Device & OS
- not provided
- Scenario
- The workbook has a master table containing address and mailer fields, and each secondary worksheet is named for a specific mailer or recipient.
- Observed behavior
- The user wants matching rows and column headers to appear automatically on each recipient sheet instead of copying formulas or records manually.
Make sure the master data is formatted as an Excel table named Table3 and that the MAILER column contains values that exactly match the recipient worksheet names. Save the workbook first, because CELL("filename",A1) needs the workbook path to return the current sheet name.
Use a dynamic array formula that matches the current worksheet name
Use this method when every recipient worksheet is named with the corresponding value in the MAILER column of the master table.
The formula reads the active sheet name, filters Table3 for rows assigned to that mailer, and stacks the table headers above the filtered records. The results spill automatically into the required number of cells.
On the master sheet, click a cell in the source table and open Table Design. Confirm that the table is named Table3 and includes the columns Full Address through MAILER.
Double-click each worksheet tab and rename it to exactly match a value in the Table3[MAILER] column, such as North Region or Mailer A.
On a recipient worksheet, select cell A1 and enter: =VSTACK(Table3[[#Headers],[Full Address]:[MAILER]],FILTER(Table3[[Full Address]:[MAILER]],Table3[MAILER]=TEXTAFTER(CELL("filename",A1),"]"),""))
Press Enter and ensure that the cells below and to the right of A1 are empty so the headers and matching rows can spill into the sheet.
Copy cell A1 to the other recipient worksheets. Each sheet will use its own tab name to return its matching Table3 rows.
Convert the filtered recipient results to static values
Use this method after reviewing a recipient sheet when you need a fixed mailing list that will no longer update with the master table.
Create Mailer-Specific Sheets Automatically in WPS Spreadsheets
WPS Spreadsheets lets you organize a master mailing table and use dynamic formulas to return records for the recipient named on each worksheet tab. Use a FILTER-based formula to create a live recipient list, then paste values when you need a fixed copy.
- 1. Open the master workbook: In WPS Spreadsheets, select Menu > Open and choose the Excel workbook containing the mailing table.
- 2. Format the source data as a table: Select the source range, then choose Insert > Table and assign a clear table name such as Table3 from the Table tools options.
- 3. Create a sheet for each mailer: Click the plus button beside the sheet tabs to add worksheets, then right-click each tab and choose Rename to enter the mailer name.
- 4. Add the filtering formula: Select A1 on a recipient sheet and enter a FILTER formula that compares the MAILER column with the current sheet name, then press Enter.
- 5. Create fixed mailing lists when needed: Select the returned cells and use Home > Paste > Paste Values to save the displayed recipient records as static values.
Frequently Asked Questions
Why does the formula return no recipient rows on a worksheet?
Check that the worksheet tab name exactly matches a value in Table3[MAILER]. Remove extra spaces and verify that the formula's table name and MAILER column reference match your actual table.
Why does TEXTAFTER(CELL("filename",A1),"]") return an error or blank result?
Save the workbook to a folder first, then recalculate the sheet. The CELL("filename",A1) function may not provide a workbook path and sheet name until the file has been saved.
Can I remove the MAILER column from the recipient-sheet output?
Yes. Change both Table3 column ranges in the formula from [Full Address]:[MAILER] to the first and last columns you want displayed. Keep Table3[MAILER] in the FILTER condition so the correct rows are still selected.
How do I make the recipient list stop updating?
Copy the complete spilled output range, then use Home > Paste > Paste Values. The formula is replaced by the displayed headers and records.
What happens when new matching rows are added to Table3?
The FILTER result expands automatically if the new row is inside Table3 and its MAILER value matches the recipient worksheet name. Keep the spill area below the formula clear so new results have room to appear.
