How to Update a Cell Based on the Last Filled Row on Another Sheet
Question details
The user needs a formula to automatically display a value from a specific column (e.g., Column N) on one sheet into a destination sheet, dynamically matching the row number where data is actively being filled.
- Product
- Excel / Google Sheets
- Device & OS
- not provided
- Scenario
- Referencing and extracting data across different worksheets or workbooks without having to manually adjust the row number in the formula.
- Observed behavior
- Instead of static references like =Sheet1!N5, the user wants a dynamic formula that automatically adapts to the current row index or the last filled data row.
Ensure you know the exact name of the source worksheet (e.g., 'Sheet1') and verify whether the source data is located in the same workbook or an entirely separate file.
Use the INDIRECT and ROW Functions (Excel & WPS Spreadsheet)
Use this solution to dynamically match the row number of your destination cell with the corresponding row in the source sheet.
The INDIRECT function evaluates text strings into valid cell references. By combining it with the ROW() function, the formula automatically detects the current row it is placed in and appends that number to the specified column letter on the other sheet.
Click on the cell in your current worksheet where you want the extracted value to appear.
Type the formula =INDIRECT("Sheet1!N"&ROW()) into the formula bar. Replace 'Sheet1' with your actual source sheet name and 'N' with the column letter containing your target data.
Press Enter to display the result. You can then click and drag the fill handle at the bottom-right corner of the cell downwards to apply this dynamic reference to subsequent rows.
Use the IMPORTRANGE Function (Google Sheets External Files)
Use this method if your source data is located in an entirely different Google Sheets file online.
Dynamically Reference Cells Across Sheets in WPS Spreadsheet
WPS Spreadsheet fully supports advanced reference functions like INDIRECT, INDEX, and MATCH, allowing you to seamlessly link data across worksheets and workbooks without manual updates.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the multiple sheets.
- 2. Navigate to Formulas: Click on the target cell, then go to the 'Formulas' tab on the top ribbon for a library of Lookup & Reference functions.
- 3. Insert the Dynamic Formula: Type your INDIRECT or LOOKUP formula to target the last filled row on the other sheet, and press Enter to instantly pull the data.
Frequently Asked Questions
How do I find the absolute last filled value in a column instead of matching the current row?
To get the last non-empty cell in a column regardless of your current row, use the LOOKUP function. For example, =LOOKUP(2,1/(Sheet1!N:N<>""),Sheet1!N:N) will return the final text or number entered in column N on Sheet1.
Why does my INDIRECT formula return a #REF! error?
A #REF! error in the INDIRECT function usually means the text string does not resolve to a valid cell address. This happens if the referenced sheet was deleted, its name was spelled incorrectly, or you forgot to add single quotes around a sheet name that contains spaces.
Can I use IMPORTRANGE in WPS Spreadsheet or Microsoft Excel?
No, IMPORTRANGE is a cloud-specific function exclusive to Google Sheets. In desktop software like WPS Spreadsheet or Excel, you reference an external file directly by keeping both workbooks open, clicking your target cell, typing '=', and then clicking the desired cell in the other workbook.
