WPS Spreadsheets Formulas for Retrieving Values from Repeating Row Blocks
Question details
Create formulas that retrieve values from repeating, noncontiguous row blocks and fill the results down automatically.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The source values are located at repeating positions such as L17, L41, L65, or at alternating positions such as L17, W17, L41, and W41.
- Observed behavior
- The required results need to follow a 24-row interval and, in the alternating pattern, switch between columns L and W after each result.
Confirm the first source row and the interval between source rows, such as row 17 followed by rows 41 and 65. Also decide whether the output should use only column L or alternate between columns L and W.
Retrieve values from column L every 24 rows
Use this formula when the source values are in column L and begin at L17, repeating every 24 rows.
The INDEX function returns a value from column L while ROW(A1) increases as the formula is filled down. The row calculation starts at 17 and adds 24 for each subsequent result.
Click the cell where the results should begin, such as A2.
Type =INDEX($L:$L,17+(ROW(A1)-1)*24) in A2, then press Enter.
Select A2 and drag the fill handle downward, or copy A2 and paste it through the required output range, such as A500.
Alternate between columns L and W in repeating row blocks
Use this formula when the desired sequence is L17, W17, L41, W41, L65, and W65.
Adjust the formula for a different starting row or interval
Use this approach when the source pattern does not start at row 17 or does not repeat every 24 rows.
Build repeating-row INDEX formulas in WPS Spreadsheets
WPS Spreadsheets can generate values from repeating row blocks with INDEX, ROW, ROWS, INT, and MOD formulas. Enter the formula in the first output cell, verify the first few references, and fill it down to automate the complete sequence.
- 1. Open your spreadsheet: Launch WPS Office, open WPS Spreadsheets, and load the workbook containing the source data.
- 2. Select the output cell: Click the first destination cell, such as A2, where the retrieved values should appear.
- 3. Enter the appropriate INDEX formula: Use =INDEX($L:$L,17+(ROW(A1)-1)*24) for column L only, or use =INDEX($L:$W,17+INT((ROWS($A$2:A2)-1)/2)*24,1+MOD(ROWS($A$2:A2)-1,2)*11) for the alternating L/W pattern.
- 4. Fill and verify the results: Copy the formula downward, then compare the first several results with L17, W17, L41, W41, or the corresponding source pattern.
Frequently Asked Questions
How do I retrieve L17, L41, and L65 in WPS Spreadsheets?
In the first output cell, enter =INDEX($L:$L,17+(ROW(A1)-1)*24), press Enter, and fill the formula down. The references increase from row 17 by 24 rows each time.
What formula returns L17, W17, L41, and W41 in order?
Enter =INDEX($L:$W,17+INT((ROWS($A$2:A2)-1)/2)*24,1+MOD(ROWS($A$2:A2)-1,2)*11) in A2 and fill it down. It alternates columns L and W and advances the source row after every two results.
Why does the alternating formula use the range L:W?
INDEX needs an array containing both source columns. Within L:W, column L is position 1 and column W is position 12, so the formula changes the column index between 1 and 12.
Can I change the starting row and interval?
Yes. Replace 17 with the first source row and 24 with the number of rows between repeating blocks. Check the first few generated references before filling the formula through the full output range.
