How to Increment Worksheet Names in Copied Excel Formulas
Question details
The user needs to automatically increment numeric worksheet names (e.g., from '725' to '726') when copying a formula down rows or across columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Dragging to copy a formula that references a specific worksheet name, expecting the sheet name to increase sequentially for each new cell.
- Observed behavior
- Excel copies the exact worksheet name as static text without incrementing it, requiring the user to manually update the sheet name reference for every copied formula.
Ensure your worksheets are named using a consistent numerical sequence before applying these formulas. Keep in mind that using volatile functions like INDIRECT across thousands of cells may impact your workbook's calculation speed.
Use INDIRECT and ROW Functions to Increment Sheet Names
Combine the INDIRECT function with a ROW or COLUMN counter to dynamically build and increment the worksheet reference text as you copy the formula.
Excel treats worksheet names in standard formulas as static text. By using INDIRECT, you can construct the reference dynamically as a text string, then use mathematical functions to increase the sheet number.
Click on the cell where you want to output the first formula result (for example, in row 14).
Type the formula =INDIRECT("'"&725+ROW(A1)-1&"'!$G$64") or =INDIRECT("'"&725+ROW()-14&"'!$G$64"). The ROW() component acts as a counter that will increase by 1 for each row.
Press Enter. The INDIRECT function will evaluate the text string into a valid reference and pull the value of $G$64 from sheet '725'.
Click the small square at the bottom-right corner of the cell and drag it down. The row calculation will increase automatically, incrementing the referenced sheet name to '726', '727', and so on.
Use the TOCOL Function for Consecutive Sheets
If you have a modern spreadsheet version and your sheets are placed consecutively, you can pull identical cell references into a dynamic array using 3D referencing.
Manage Dynamic Sheet References Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced mathematical and lookup functions, including INDIRECT, ROW, and TOCOL. You can effortlessly create dynamic, incrementing worksheet references with seamless compatibility with Microsoft Excel formulas.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your sequentially numbered worksheets.
- 2. Input the dynamic formula: Select your target cell and type your preferred =INDIRECT() formula.
- 3. Apply and drag: Press Enter, then grab the fill handle in the bottom-right corner and drag it down to automatically populate the incremented sheet references.
Frequently Asked Questions
Why doesn't Excel increment sheet names automatically when dragging a formula?
In Excel, a worksheet name within a formula reference is treated as a static text string rather than a numerical value. Because Excel only increments relative cell references (like A1 to A2), the text string representing the sheet name remains unchanged when copied.
What does it mean that the INDIRECT function is 'volatile'?
A volatile function recalculates every single time any change is made anywhere in the workbook, not just when its precedent cells change. If used extensively across thousands of cells, INDIRECT can significantly slow down your workbook's performance and calculation times.
Can I increment worksheet names if my sheets contain letters, like 'Sheet1', 'Sheet2'?
Yes. You can modify the INDIRECT formula to concatenate the text part with the dynamic number. For example, use =INDIRECT("'Sheet" & ROW(A1) & "'!$G$64"). As you drag the formula down, ROW(A1) becomes ROW(A2) evaluating to 2, updating the reference to 'Sheet2'.
