Can I make a structured reference absolute in excel 07?


Answers

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/

Question

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




Links



Tags