How to Reference Every Fifth Row on Another Sheet in Excel
Question details
The user needs a formula to pull consecutive data from a source worksheet and display it at five-row intervals (with blank rows in between) in a destination worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting or migrating data where the destination layout requires spacing between consecutive source records, avoiding the manual typing of each cell reference.
- Observed behavior
- The goal is to dynamically return consecutive source rows (e.g., A3, A4, A5) into spread-out destination cells (e.g., B3, B8, B13) seamlessly.
Verify the exact name of your source data sheet (e.g., 'Orig') and identify the precise starting cell for both your continuous source data and your spaced destination range.
Use INDEX and QUOTIENT Functions (Best for Spaced Destinations)
This method calculates the correct consecutive source row number based on the current destination row, allowing you to copy the formula down while effortlessly maintaining the five-row gap.
By pairing the INDEX function with QUOTIENT and ROW, you can increment the source row index exactly once every five rows in the destination sheet. This approach is highly preferable to using INDIRECT because it is non-volatile and processes much faster.
Click on the first cell in your destination sheet where you want the first piece of data to appear (for example, B3).
Type the formula =INDEX(Orig!$A:$A,3+QUOTIENT(ROW()-ROW($B$3),5)) into the formula bar. Replace 'Orig' with the actual name of your source worksheet.
Press Enter to apply the formula. Then, click the fill handle at the bottom-right corner of the cell and drag it down to populate the remaining rows. The formula will naturally increment the source reference only after every five destination rows.
Use Dynamic Array Functions (For Extracting Nth Rows)
If your goal is reversed—extracting every 5th row from a spaced source into a continuous destination list—modern dynamic arrays offer a clean solution.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Office Spreadsheet provides full support for advanced functions like INDEX, QUOTIENT, and modern dynamic arrays, making it incredibly simple to reference, reorganize, and analyze complex data layouts.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your spaced data layout.
- 2. Enter the Formula: Navigate to the desired destination cell and type in the INDEX and QUOTIENT formula provided in the solution.
- 3. Drag to Fill: Use the intuitive fill handle to drag the formula down; WPS will process the references instantly without lag.
Frequently Asked Questions
Can I modify the formula to reference every 3rd row instead of the 5th?
Yes, simply change the number 5 in the QUOTIENT function to 3. For example: =INDEX(Orig!$A:$A,3+QUOTIENT(ROW()-ROW($B$3),3)). This will adapt the interval to three rows.
Why use the QUOTIENT function instead of the INDIRECT function?
INDIRECT is a volatile function, meaning it recalculates every single time any change is made in the workbook, which can drastically slow down processing on large sheets. Using QUOTIENT combined with INDEX is non-volatile, making it significantly more efficient and stable.
What if my source data starts on a different row?
You need to adjust the starting row offset in the formula. If your source data starts on row 2, change the '3+' part of the formula to '2+'. Also, ensure the $B$3 reference matches your actual starting destination cell.
Will this formula break if I insert rows above my destination data?
Because the ROW() reference is dynamic and relative to your anchor cell (e.g., $B$3), inserting rows above it generally shifts the references correctly. However, if you delete the actual anchor row, the formula may return a #REF! error.
