0

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.

Travis Bennett
  • 131
  • 3
  • 12

1 Answers1

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))
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • This is exactly what it took. I ended up used Defined Named or Named Ranges for some long formulas, and String Substitution methods for others, when in excess of 255 characters. – Travis Bennett Sep 24 '18 at 11:52