How to Increase Sheet Numbers While Keeping a Cell Reference Fixed
Question details
The user wants to drag a formula down a column so that the referenced worksheet number increments sequentially (e.g., Sheet1, Sheet2) while the targeted cell address remains fixed.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Pulling data from the exact same cell across multiple sequential worksheets without manually editing the sheet name in each row.
- Observed behavior
- Needs a dynamic formula structure to automatically increment the sheet name number while locking the referenced cell.
Verify that your worksheet names follow a strict sequential naming pattern (like Sheet1, Sheet2, Sheet3) without any typos or irregular spacing.
Use INDIRECT and ROW Functions
Combine the INDIRECT and ROW functions to dynamically generate the worksheet reference string while locking the destination cell.
The INDIRECT function converts a text string into a valid cell reference. By integrating the ROW function into the text string, you generate a number that increases automatically as you copy the formula downward. This dynamically updates the target sheet name while maintaining your designated fixed cell.
Click on the cell in your summary sheet where you want the first referenced value to be displayed.
Type the formula: =INDIRECT("Sheet"&ROW($A1)&"!$G$7"). The ROW($A1) returns 1, which evaluates the text string to Sheet1!$G$7.
Click and hold the fill handle (the small square at the bottom right corner of the active cell) and drag it down. The ROW($A1) reference will automatically change to ROW($A2), ROW($A3), etc., effectively pulling data from Sheet2!$G$7, Sheet3!$G$7, and so on.
Seamlessly Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functional logic like INDIRECT and ROW combinations, allowing you to efficiently manage multi-sheet workbooks. It's a lightweight, high-performance tool designed for professional data analysis.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your computer.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your multiple worksheets.
- 3. Apply the Function: Use the exact =INDIRECT("Sheet"&ROW($A1)&"!$G$7") syntax to pull your data seamlessly.
Frequently Asked Questions
Why does the INDIRECT formula return a #REF! error?
A #REF! error typically occurs if the referenced sheet does not exist, if the sheet name is spelled differently than in your formula, or if you forgot to include single quotes for sheet names with spaces. Double-check your actual worksheet tabs to ensure they match the text string in the formula perfectly.
How do I increment the sheet number while dragging the formula horizontally?
If you need to drag the formula across columns instead of down rows, replace the ROW function with the COLUMN function. For example, use =INDIRECT("Sheet"&COLUMN(A$1)&"!$G$7"). As you drag horizontally to the right, A$1 will change to B$1, incrementing the number from 1 to 2.
Can I lock a different cell reference instead of G7?
Yes, simply replace $G$7 in the formula text string with the cell address you want to reference. Because the cell address is enclosed in quotation marks, it acts as a static text string and will naturally remain locked as you drag the formula in any direction.
Will this formula slow down my spreadsheet?
INDIRECT is considered a 'volatile' function, meaning it recalculates every time any change is made anywhere in the workbook. While small datasets will run perfectly fine, using thousands of INDIRECT formulas might cause minor performance slowdowns in very large workbooks.
