$ (dollar sign) for structured notation (Excel worksheet table)

Question

I am basically looking for the equivalent of $ A: $ A for structured links in a table in Excel.

Let's 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 (therefore, each column is a survey question, more or less). tChoice is a smaller table (just a few columns), I basically want to look in t Select the value of the raw data and return the label based on this (in the table of values-tables - tChoice).

I really want tData [@ [cm_sex]] to automatically increase when I apply the formulas in the cells on the left (so I look at all the columns of the source data), however I DO NOT want the tChoice [name] column to change: for example, the search column matching based on the original table data.

This is equivalent to writing, say, A: A (which will automatically increase to B: B, C: C, etc.) and $ A: $ A (which is not the case).

Is there an equivalent dollar sign for referencing structured tables?

PS: Of course, I can do other things, such as increasing everything than finding and replacing a range, with tchoice [*] replaced by tChoice [name] ... However, it would be cleaner and more efficient to have the proper naming for it. ...

Did not find it on 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 an answer in the comments. I have never seen this before, thanks to him or her for this answer. There is some information on the Internet about absolute links to structured tables, so I thought I would summarize what I found.

In your situation, you can use tChoice[[name]:[name]] Defining a range that only one column fixes the column, like the $ signs, in normal cell references.

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

Now say that you want to snap one cell and the other relative, as in this scenario, where I sum from a to the right, starting with a: a, then a: b, etc.:

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

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

Note that these formulas are draggable and not copied. Perhaps there is a keyboard shortcut that does this.

This process is quite awkward compared to just clicking F4, as with a normal cell reference. John Acampora created addin, which automates this process, as well as two detailed posts on this topic. His first post contains a link to the link with the addition.