I haven’t found a way to make the column reference absolute when using a reference like A:A or SheetName!A:A. Typically, you’d just use $A:$A or SheetName!$A:$A, but that is currently resulting in an error. I could use $A$1:$A$100, but then I can’t add rows to the bottom of the table and expect the references to update to include the new rows.
Work around: I’ll place a blank row at the bottom of the table with a note to “Insert rows above this line” so that when inserting a row, the references will stretch.
A structured table reference would help solve this type of thing.