WPS Spreadsheets: Fill Excel References in an Alternating Row Pattern
Question details
The user needs to fill formulas downward while alternating references between two source columns and moving to the next source row after each pair.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A formula starts in a target cell such as B2 and should return values from Historic!F175, Historic!G175, Historic!F176, Historic!G176, and so on.
- Observed behavior
- Dragging a standard formula increments source row numbers incorrectly instead of alternating between the two source columns for each source row.
Confirm the target starting cell and the two-column source range on the Historic sheet. Use an explicit source range that covers the required rows rather than an unnecessarily large range when possible.
Use INDEX, QUOTIENT, and MOD to alternate source columns
Use this formula when you need a reliable fill-down pattern that returns two source columns for each source row.
The row calculation advances once every two target rows, while the column calculation alternates between the first and second source columns. Adjust the starting cell and source range to match your worksheet.
Select the starting target cell, such as B2, and enter =INDEX(Historic!$F$175:$G$1000000,QUOTIENT(ROW()-ROW($B$2),2)+1,MOD(ROW()-ROW($B$2),2)+1).
Press Enter, reselect the formula cell, and drag its fill handle downward, or use Home > Fill > Down after selecting the formula cell and the destination range.
Verify that the results follow Historic!F175, Historic!G175, Historic!F176, Historic!G176, and continue in the same pattern.
Use TOCOL for a dynamic array in Excel 365
Use this option when the spreadsheet supports TOCOL and you want the complete two-column range to spill automatically into one column.
Fill Alternating References Easily in WPS Spreadsheets
WPS Spreadsheets can create the alternating reference pattern with INDEX, QUOTIENT, and MOD, then fill the formula down while preserving the intended source-row and source-column sequence.
- 1. Open the workbook: Open the Excel-compatible workbook in WPS Spreadsheets and locate the target cell and Historic source sheet.
- 2. Enter the formula: In the starting target cell, enter =INDEX(Historic!$F$175:$G$1000000,QUOTIENT(ROW()-ROW($B$2),2)+1,MOD(ROW()-ROW($B$2),2)+1), replacing B2 and the source range when needed.
- 3. Fill downward: Use the fill handle or Home > Fill > Down to copy the formula through the required destination cells.
- 4. Verify the pattern: Check that each source row contributes its first-column value followed by its second-column value before the next source row begins.
Frequently Asked Questions
Why does dragging my formula skip or change the source rows incorrectly?
Relative references usually increase when a formula is filled down. Use the INDEX formula with QUOTIENT and MOD so the source row advances only after two target rows and the source column alternates between F and G.
How do I change the formula if my starting cell is not B2?
Replace both occurrences of ROW($B$2) with the actual starting cell. For example, if the formula starts in C5, use ROW($C$5) in both locations.
What sequence does the INDEX formula return?
For the source range Historic!$F$175:$G$1000000, the results are F175, G175, F176, G176, F177, G177, and so forth.
Can I use TOCOL instead of INDEX?
Yes, when TOCOL is supported, use =TOCOL(Historic!F175:G299,,FALSE). The FALSE setting scans by rows, which keeps each source row's two column values together.
What should I do if the formula returns an error near the end of the list?
Make sure the source range contains enough rows for the number of filled target cells. Also check that the Historic sheet name and the starting-cell reference match your workbook.
