Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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)

like image 585
logicOnAbstractions Avatar asked Aug 20 '15 00:08

logicOnAbstractions


People also ask

What does '$' mean in Excel formula?

The dollar sign fixes the reference to a given cell, so that it remains unchanged no matter where the formula moves. In other words, using $ in cell references allows you to copy the formula in Excel without changing references.

What is $$ in Excel?

In an absolute reference, each part of the reference (the letter that refers to the row and the number that refers to the column) is preceded by a “$” – for example, $A$1 is an absolute reference to cell A1. Wherever the formula is copied or moved, it always refers to cell A1.

How do you put dollar sign in Excel?

To do this, you'll need to change the cell reference to an absolute reference by adding dollar signs before the row and column (for example, $D$2). Although you can type the dollar signs manually, the F4 key on your keyboard allows you to add both dollar signs with a single keystroke.

How do you reference a structured table in Excel?

To include structured references in your formula, click the table cells you want to reference instead of typing their cell reference in the formula. Let's use the following example data to enter a formula that automatically uses structured references to calculate the amount of a sales commission.

How do I fix a dollar sign in a row in Excel?

If you want to maintain the original cell reference when you copy it, you "lock" it by putting a dollar sign ($) before the cell and column references. For example, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same.

Why is dollar sign used in Excel?

Using dollar sign in excel can change the referencing in the formula. Excel's default is to use relative reference when using formulas. It means when a formula is copied and pasted into another cell, the formula will change its reference points by the exact number of columns and rows to that you moved the formula.


1 Answers

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:

enter image description here

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.

like image 150
Doug Glancy Avatar answered Sep 20 '22 16:09

Doug Glancy