Menu
I have a table "A" with 2 columns "Foo" and "Bar". I have a formula with the structured reference A[Foo]. When I fill this formula horizontally I want the reference to stay A[Foo] but now, in the second column, the reference turns to A[Bar]. Is there a way to make this structured reference absolute?
It'd be shocking that this isn't supported if not.
Example Formula:
=A[Foo]
Drag that horizontally and Foo changes if the table has multiple columns
522
Most Excel users figure out how to lock these references by either toggling through the options with the F4 key or using the $ (dollar sign) symbol in front of the column and/or row references.
Absolute references You can use an absolute reference to keep a row and/or column constant. An absolute reference is designated in a formula by the addition of a dollar sign ($) before the column and row. If it precedes the column or row (but not both), it's known as a mixed reference.
Use the F4 key The F4 key is the easiest way to lock cell references. Simply select the cell or range of cells that you want to lock, then press the F4 key. Excel will automatically add the $ sign to the appropriate places in the cell reference. One advantage of using the F4 key is that it's quick and easy.
You can make the reference absolute by duplicating the reference to column as if it were a range. Looks like the following:
A[[Foo]:[Foo]]
Put A[[Foo]:[Foo]] in your formula and drag across. The reference will remain on the [Foo] column. Note, you have to drag the formula. Copy/paste won't work.
This also works if you want an absolute reference on a cell in the same row of the same table. Assuming the formula is in the Table named "A", the following will anchor the cell in the same row of the formula.
A[@[Foo]:[Foo]]
There is an article and video on my site about this issue where I attempt to explain it clearly. :)
http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
