Can I make a structured link absolute in excel 07?

Question

I have a table "A" with 2 columns "Foo" and "Bar". I have a formula with a structured link A [Foo]. When I fill this formula horizontally, I want the link to remain A [Foo], but now in the second column the link turns into [Bar]. Is there any way to make this structured link absolute?

It would be shocking that this is not supported, if not.

Formula example:

= A [Foo]

Drag it horizontally and Foo if multiple columns are specified in the table.

Answer 1

There is a difference between copy and drag. Drag and drop is also called "fill" formulas. On the Microsoft support website, you will find the following explanation:

Moving, copying, and filling out structured links

All structured links remain when you copy or move a formula that uses a structured link.

When you complete the formula, fully qualified structured links can adjust column qualifiers as series, as described in the table below.

So, this basically means that the behavior you see is by design. If you want the link to be absolute, you must copy the formula, not drag / drop it.

Answer 2

You can make the link absolute by duplicating the column link, as if it were a range. Looks like the following:

A [[Foo]: [Foo]]

Place A [[Foo]: [Foo]] in the formula and drag it. The link will remain in the [Foo] column. Note: you need to drag the formula. Copy / paste will not work.

This also works if you want an absolute cell reference in the same row of the same table. Assuming the formula is in a table named “A,” the following will snap the cell in the same line of the formula.

And [@ [Foo]: [Foo]]

On this site there is an article and a video about this problem, where I try to explain it clearly. :)

http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/

Answer 3

I could not create the idea of Control + drag . However, by selecting the source cell and the ones you want to fill, and using Control + R , fill in correctly, the shortcut seems to have copied without changing the structured links in columns.

Answer 4

It looks like you cannot make a structured link (.ie - A [Foo]) absolute. If you want to use absolute references in your formula so that you can use the fill handle, use the old absolute reference method ($ A $ 2 or $ A2)

Example:

Table Name: DiscountPricing

  ABCD 1 Item Base Price 5% 10% 2 Pencil $0.50 =[Base Price]-([Base Price]* =[5%]-([5%]* DiscountPricing[[#Headers],[5%]]) *DiscountPricing[[#Headers],[10%]]) 3 Pen $1 =$B3-($B3* =$B3-($B3* *DiscountPricing[[#Headers],[5%]]) *DiscountPricing[[#Headers],[10%]]) 

See more information: http://www.technicalcommunicationcenter.com/2011/05/31/how-to-use-structured-references-in-ms-excel/

Answer 5

Try inserting a table name with an INDIRECT function, for example;

 INDIRECT("Table Name[Column Heading]") 

In your case INDIRECT("A[Foo]")

Now you can drag it horizontally and the column reference remains static!

Answer 6

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

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

Answer 7

sounds like your still drag / drop, the target is for the bottom of the cell, not in the corner. Ross