Excel Hates Defined Name for Formula referring to Same Row in Table Format, e.g. [@[TableColumnName]]

Question

When I try to make a defined name (Formulas > Defined Name > Name Manager > etc.) representing the local or same row in a table, e.g. =[@[TableColumnName]], Excel won't let me, says "The syntax of the name isn't correct."

I know by trial and error that the issue is my Refers To box and not the Name of the Defined Name (as the error message quoted above implies).

However, I need to include a reference to fields in the same table row as the current in a Defined Name, to shorten my formulas. What's the best way? Is there an alternative notation?

Why? I'm trying to shorten my Excel Array Formulas to < 255 characters, so they can be placed programmatically in VBA with the Range.FormulaArray.

Answer 1

I don't think you can use a structured reference for a defined name formula like that.

Try:

=INDEX(Table1[#All],ROW(),1)

where the column index 1 is the same column as [TableColumnName]. If that column location might be variable, then use:

 =INDEX(Table1[#All],ROW(),MATCH("TableColumnName",Table1[#Headers],0))

And, if the table might start in other than Row 1 (this is probably the most adaptable to different types of data configurations):

=INDEX(Table1[#All],ROW()-ROW(Table1[#Headers])+1,MATCH("TableColumnName",Table1[#Headers],0))