views:

1147

answers:

3

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

A: 

It seems that if you hold down Ctrl while copying the formulas, it will remain static/absolute.

http://office.microsoft.com/en-us/excel/HA101556861033.aspx

cLFlaVA
Hmm, well I had seen that link. It doesn't work for me though. I can manually copy and paste but I can't drag the columns horizontally. Have you verified this your self? I just wanna make sure I'm not doing anything wrong.
TheDeeno
+1  A: 

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.

0xA3
Thanks for the clarification. Unfortunately, unless I'm doing something wrong, even filling with CTRL adjusts the formulas like a series. Does it work for you? Looks like I'll just be copying.
TheDeeno
I adjusted the question to use the right semantics.
TheDeeno
This has nothing to do with pressing Ctrl or not. Just copy and paste the cell and you will the reference be the same.
0xA3
I'm with ya. Thanks.
TheDeeno
A: 

sounds like your still dragging/filling, aim for the bottom of the cell, not the corner. Ross