No Absolute Column References?

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.

Rich “structured table reference” is being planned. For eg: [@Salary]. That would refer the range from top of the table to the bottom of the table.

Let me get back on the first half of the query. Or I’ll let @msiraj answer it.

$A:$A & SheetName!$A:$A should correctly evaluate in SSDC.

However, there seems to be a formula cell-editor issue which is corrupting such formulas on edit =SUM($C:$C)) (i.e. adding an additional parenthesis). As a workaround request you to update formula via formula bar. We will address the formula cell-editor issue soon, tracking it as: #001585

Issue #1585 was addressed in the June 6th release: formula editor assistant can now handle absolute references such as =SUM($C:$C)

Meanwhile, our plans for structured table references remain as part of the short/medium term roadmap.

I’ve noticed that not only can you use an absolute column reference like $A:$A, but you can also use a reference like $A$4:$A to start at row 4 and capture all newly added rows. This type of reference also works in Google Sheets (but not Excel), so kudos for making this work!

3 Likes