I have a simple Excel table containing a formula that is referencing columns in another table, and is using the automatic table-range-names (not sure what they're correctly called)
=A1 + SUM(MyOtherTable[ColumnA]) * SUM(AnotherTable[AnotherA])
The problem is that when I copy that formula to the right, the column references all shift right too, so becoming:
=A1 + SUM(MyOtherTable[ColumnA]) * SUM(AnotherTable[AnotherA])
=B1 + SUM(MyOtherTable[ColumnB]) * SUM(AnotherTable[AnotherB])
=C1 + SUM(MyOtherTable[ColumnC]) * SUM(AnotherTable[AnotherC])
How can I stop this? I can't find an equivalent to the dollar-references like $A$A
. Note that I do want the normal behaviour for the RC references.
The only alternative I can think of is to created named ranges for the columns in the other tables, although this seems to defeat the object to some extent of the table-ranges.