Menu
Trying to use Microsoft's fancy new table reference system, using table names, @'s and #'s to reference a specific row in my table. The only documentation and examples I can find tell me either how to reference specific columns, or reference the pre-made Headers or Totals rows.
I came across what every source calls "@ or [#This Row]" but I have no idea what that means, and putting the @ before my row name isn't working...
[removed ascii table, simplified below]
I can use the old reference system, but all my other formulas use the new one and I want to use it for the sake of uniformity, and just for the simple fact that gosh darn it I should be able to reference rows like this!
EDIT: My formulas are in separate tabs than my tables. I can use the new table reference system to reference only pre-defined "special" rows, such as the Totals row. But I have to use the old reference method '[tab_name]'![XY] when I want to reference a specific row other than the fancy Totals row.
Here is my formula which references the Totals row in the "Groceries" table in a separate tab than my formula. As you can see, I can use the Table_Name[[#Totals],[Column_Name]] syntax just fine:
But I can't figure out how to reference specifically the "Costco" row in that table- Excel automatically uses the old syntax:
Here's the table for reference:
Does Excel 2016 really not have a way to reference rows in this new system? That seems like a pretty huge oversight in design...
975
The @ symbol inside a table means "this row". You can combine this with a column name to reference a particular cell in the current row. To reference the Total row, use the #Totals specifier. To reference a specific total, use a double set of brackets, and the column name, just like the headers row.
To create a range reference, select a range of cells on the worksheet. To refer to the whole row or entire column, click the row number or the column letter, respectively.
Referencing a specific column is accomplished like this: TableName[ColumnName]. For example, to refer to the Q1 column in the DeptA table, we would use: DeptA[Q1]. In addition to referring to specific columns, we can refer to specific rows.
It is a built-in function and takes only one argument as the reference. The method to use this function is as follows: =ROW( Value ). It will only show the cell's row number, not its value. For example, =ROW(A5) returns 5 because A5 is the fifth row in the Excel spreadsheet.
Use the INDEX() function, where the named table is your input for "array". You then only need to specify the row for a table with one column, or row and column for an n by m table.
You can also specify a field within the table as your array, and then specify just the row number.
For example, if you have a table called "Birthdays" with the columns "Name" and "Birth year": "Birthdays" table
The following two syntax options can be used to return the second value under "Birth year":
=INDEX(Birthdays,2,2)
=INDEX(Birthdays[Birth year],2)
70
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
