$ (dollar sign) equivalent for structured notation (Excel Worksheet table)

Question

I'm basically looking for a $A:$A equivalent for structured table references in Excel.

Say I have this formula:

=INDEX(tChoice,MATCH(OFFSET(tData[@[cm_sex]],-3,0),tChoice[name],0),3)

Basically tData is a table full of raw data (many columns), taken from surveys (so each column is Survey question, more or less). tChoice is a smaller table (just a few columns), I basically want to look up into tChoice the raw data value & return a label based on that (to value-label table is tChoice).

I actually want the tData[@[cm_sex]] to auto-increment as I apply formulas in cells to the left (so I cycle through all the columns of the raw data), however I DON'T want the column tChoice[name] to change: e.g. the column to look for a match based on the raw table data.

This is equivalent to writing, say, A:A (which would auto-increment to B:B, C:C, etc) and $A:$A (which would not).

Is there a dollar sign equivalent for structured table references?

P-S: Of course I can other things like increment the whole thing, than search & replace the range with say tChoice[*] replaced by tChoice[name]... However it would be cleaner & more efficient to have a proper notation for it....

Didn't find it in the support pages (https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e)

Answer 1

user3964075 provided the answer in the comments. I had never seen this before so thanks to him or her for this answer. There's some information out there on the web about absolute structured table references, so I thought I'd summarize what I found.

For your situation you can use tChoice[[name]:[name]] Specifying a range that's just the one column anchors the column like $ signs do in normal cell references.

If you want to just deal with one row (the one that the formula is in) the anchor looks like this:tChoice[@[name]:[name]].

Now say you want to anchor one cell but have the other be relative, as in this scenario where I'm summing from a to the right, starting with a:a, then a:b, etc:

You can do that with a formula like this, where the first part is absolute and the second is relative:

=SUM(Table1[@[a]:[a]]:Table1[@a])

Note that these formulas much be dragged, not copied. Perhaps there is a keyboard shortcut that does this.

This process is rather clunky compared to just clicking F4, as with a regular cell reference. Jon Acampora has created an addin that automates this process, as well as two detailed posts on this topic. His first post contains a link to the one with the addin.