'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Automatically Increment Excel Sheet References with a Formula
Question details
The user needs a way to dynamically increment worksheet references (e.g., from Sheet 1 to Sheet 2) in a formula when dragging it across rows or columns.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Pulling data from multiple sequentially numbered worksheets into a main summary sheet.
- Observed behavior
- Standard cell references do not automatically update or increment the worksheet name when a formula is filled down or across.
Ensure your worksheets are sequentially named (e.g., 1, 2, 3) and that you know the exact target cell you want to extract data from on each sheet.
Use INDIRECT and ROW for Vertical Auto-Incrementing
Combine the INDIRECT function with the ROW function to dynamically generate sequential sheet names as you drag the formula down a column.
The INDIRECT function converts a text string into a valid cell reference. By using the ROW function, you can create a counter that increases by 1 for each row. This allows the formula to reference the next sequential worksheet automatically.
Click on the cell in your master sheet where you want the first piece of data to appear, for example, cell D2.
Type =INDIRECT("'"&ROW(D2)-ROW($D$2)+1&"'!B2"). This formula assumes your sheets are named exactly '1', '2', '3', etc.
Click and hold the fill handle at the bottom-right corner of the cell, then drag it down to automatically increment the reference to subsequent sheets.
Use INDIRECT and COLUMN for Horizontal Auto-Incrementing
If you are dragging your formula horizontally across a row, use the COLUMN function combined with INDIRECT to increment the sheet reference.
Effortlessly Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced functions like INDIRECT, ROW, and COLUMN, allowing you to easily pull and summarize data across multiple worksheets.
- 1. Open your workbook: Launch WPS Office and open your spreadsheet file containing the sequential sheets.
- 2. Enter the INDIRECT formula: Select your master sheet cell and input the INDIRECT and ROW combination formula to target your sequence.
- 3. Drag to fill: Use the intuitive fill handle to drag down or across, instantly pulling data from multiple sheets.
Frequently Asked Questions
Why am I getting a #REF! error when using the INDIRECT formula?
A #REF! error usually occurs if the worksheet name generated by the formula doesn't match the actual sheet name exactly, or if the referenced sheet name contains spaces but isn't enclosed in single quotes within the formula string.
Can I use this method if my sheet names are text instead of numbers (e.g., Jan, Feb, Mar)?
Yes, but you will need to list the sheet names sequentially in a separate column or row on your master sheet. You can then reference those specific text cells within your INDIRECT formula instead of using the ROW or COLUMN math.
Does the INDIRECT function update when a referenced sheet is renamed?
No. Because the INDIRECT function evaluates a text string, it will not automatically update if you change the actual sheet's name. You must manually update the text string in the formula to match the new name.
Is the INDIRECT function considered volatile?
Yes, INDIRECT is a volatile function. This means it recalculates every time any change is made anywhere in the workbook, which can slow down performance if used extensively in very large files.
