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
It seems that if you hold down Ctrl while copying the formulas, it will remain static/absolute.
Hi,
there is a difference between copying and dragging. Dragging is also called "filling" formulas. At the URL that cLFlaVA posted (http://office.microsoft.com/en-us/excel/HA101556861033.aspx) you will find the following explanation:
Moving, copying, and filling structured references
All structured references remain the same when you copy or move a formula that uses a structured reference.
When you fill a formula, fully qualified structured references can adjust the column specifiers like a series as summarized in the following table.
So it basically means that the behavior you see is by design. If you want the reference to be absolute you should copy the formula and not drag/"fill" it.