Use Excel Structured References Outside the Current Table Row in WPS Spreadsheets
Question details
The user needs to understand how Excel table structured references work and how to write formulas that refer to rows, columns, and positions other than the current table row.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with an Excel table in WPS Spreadsheets and needing formulas that reference the previous row, a fixed row, or the range from the table header to the current row.
- Observed behavior
- The goal is to use structured references such as OFFSET([@ID],-1,0), INDEX([ID],4,1), and ROWS([[#Headers],[ID]]:[@ID]) to return values outside the current table row.
Before you write structured reference formulas, confirm that your data is formatted as a WPS Spreadsheets table (Insert > Table) and note the exact table and column names used in the formulas. This ensures references such as [@ID] and [ID] resolve correctly.
Reference Another Row with OFFSET in a WPS Spreadsheets Table
Use OFFSET when you need the previous row, next row, or any relative position from the current table row.
OFFSET works with structured references by starting at the current-row cell and returning a cell that is a specified number of rows and columns away.
Click the cell in the table column where you want the formula result to appear.
Type =OFFSET([@ID],-1,0) in the formula bar and press Enter to return the ID value from the row above the current row.
Return a Fixed Table Row with INDEX in WPS Spreadsheets
Use INDEX when you need a specific row number in a table column, independent of the current row.
Count Rows from the Table Header to the Current Row with ROWS
Use ROWS with a header structured reference when you need a dynamic row count from the table header through the current row.
Use Structured References in WPS Spreadsheets to Reference Other Table Rows
WPS Spreadsheets supports Excel-style structured references, so you can use OFFSET, INDEX, and ROWS formulas directly inside table columns to reference the previous row, a fixed row, or the range from the header to the current row.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx workbook that contains the Excel table, or create a new spreadsheet and insert a table via Insert > Table.
- 2. Select a cell inside the table column: Click an empty cell in the table column where you want the formula result, or select the cell in the table that will contain a calculated column.
- 3. Enter a structured reference formula: Type formulas such as =OFFSET([@ID],-1,0), =INDEX([ID],4,1), or =ROWS([[#Headers],[ID]]:[@ID]) in the formula bar and press Enter.
- 4. Confirm the table range and calculation: Check that WPS Spreadsheets recognizes the table and that the formula updates correctly when rows or table data change.
Frequently Asked Questions
Can I reference the previous row in an Excel table without using regular cell references?
Yes. Use =OFFSET([@ID],-1,0) in WPS Spreadsheets to return the value from one row above the current row in the ID column.
How do I get a specific row from a table column in WPS Spreadsheets?
Use =INDEX([ID],4,1) to return the 4th row from the ID column. Adjust the row number or column number as needed.
How can I count rows from the table header to the current row?
Use =ROWS([[#Headers],[ID]]:[@ID]) to count the rows from the table header through the current row in the ID column.
Do WPS Spreadsheets support Excel structured references?
Yes. WPS Spreadsheets supports Excel table structured references such as [@Column] and Table1[Column], which helps maintain compatibility with .xlsx workbooks.
What does [@ID] mean in a structured reference?
[@ID] refers to the value in the ID column on the current row, while [ID] refers to the entire ID column data range.
