How to Populate Repeating Excel Badges from a Source List in WPS Spreadsheets
Question details
The user needs to populate a repeating badge layout from a single source row in an Excel list, so each badge block uses the correct source row instead of autofill incrementing the reference incorrectly.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating a sheet of repeating name or ID badges from a source list, where each badge spans multiple rows and columns and must pull data according to a fixed badge height.
- Observed behavior
- Ordinary autofill increments the source reference incorrectly, so the formula must calculate the source row from the destination row and the fixed badge height.
Open your workbook in WPS Office Spreadsheets and confirm that each badge uses a fixed number of rows and that you know the first badge row and the source sheet name.
Calculate the source row with ROW() and INDEX in WPS Spreadsheets
Use this when you need a live badge sheet formula that automatically maps every badge cell to the correct row in the source list.
This method avoids the broken autofill pattern by making each formula compute its source row from the current row number, the fixed badge height, and the first badge row. It works when the badge layout is consistent.
In WPS Office Spreadsheets, click the first worksheet cell that should display the source name, typically the top badge name cell.
Type =INDEX(Sheet2!$A:$A,INT((ROW()-firstBadgeRow)/7)+2) in the formula bar, replacing Sheet2, A:A, firstBadgeRow and 7 with your source sheet, source column, first badge row and badge height.
Press Enter, then drag the fill handle or copy and paste the formula into every badge cell that must repeat the same source row sequence.
Compare several badge cells against the source list to confirm that each block of rows returns the intended source row and name.
Create badge sheets by merging the Excel list in WPS Writer
Use this for large badge runs or double-sided output when you want easier page layout and print control than a formula-based sheet.
Populate Repeating Badge Sheets in WPS Office
Use WPS Office Spreadsheets to calculate badge rows with ROW() and INDEX so every badge cell pulls the correct name from your Excel-compatible source list automatically. For larger print runs, WPS Writer Mail Merge gives you layout and two-sided printing control.
- 1. Download and install WPS Office: Download WPS Office Free from the official WPS website and install it on your computer.
- 2. Open your badge workbook: Launch WPS Office Spreadsheets and open the Excel .xlsx workbook that contains the badge layout and source list.
- 3. Apply the ROW/INDEX formula: In the first badge cell, enter =INDEX(source_sheet!$A:$A,INT((ROW()-first_badge_row)/badge_height)+source_start_row) and press Enter.
- 4. Fill and verify the sheet: Copy the formula across the badge layout, then verify that each badge block now advances to the next source row correctly.
- 5. Print or mail merge: Print directly, or use WPS Writer Mail Merge for large runs and double-sided badge output.
Frequently Asked Questions
Why does normal autofill break my badge formula?
Because autofill increments the cell reference rather than recalculating which source row should feed the current destination row. A ROW()-based formula calculates the source row from the badge row position and fixed badge height.
How do I adjust the formula if my badges are not 7 rows tall?
Replace the 7 in INT((ROW()-firstBadgeRow)/7)+2 with your actual badge height, and adjust the +2 to the first source data row after the header.
Can WPS Writer Mail Merge use my Excel badge source list?
Yes. In WPS Writer, use Mailings > Mail Merge to select the Excel workbook as the data source, then insert the name or ID field and finish the merge.
What happens if my source list has blank rows?
The INDEX formula may return 0 for blank source rows. You can remove blank rows from the source list, or wrap the formula in IF to suppress the zero.
Is this formula compatible with Microsoft Excel?
Yes. The workbook and formulas use standard Excel functions, so you can open and edit the same badge file in WPS Spreadsheets and Microsoft Excel.
