In MS Excel 2007 there are 3 types of cell references: absolute, relative and mixed. The use of correct type of reference provides correct behavior when dragging of the formula using references to other cells:
<ol>
<li><strong>Absolute</strong> e.g. $A$1
An absolute reference is not changed while dragging. For instance in cell A1 is number 100. In cell B1 we enter a formula <code>=$A$1</code>. Dragging of B1 to B2 or C1 will not change the behavior of the formula in B2 or C1 i.e. all 3 cells with the formula will show value 100.
</li>
<li><strong>Relative</strong> e.g. A1
A relative reference is changed in both directions while the formula is dragged. Let A1 is containing number 100, A2=200 and B1=300. We enter the formula formula <code>=A1</code> in C1. Afterwards we drag the formula into C2 and D1. The formulas in these cells will be altered so that C2 and D1 will contain formulas <code>=A2</code> (shows 200) and <code>=B1</code> (shows 300) respectively.
</li>
<li><strong>Mixed</strong> e.g. $A1
A mixed reference is dynamically changed in one direction only e.g. $A1 has a absolute column and relative row reference.
</li>
</ol>
The problem is that such notation is not working when using tables in Excel 2007. We want to find out how to make a absolute reference to the whole column of a table.
The syntax of relative reference to Column1 of Table1 is <code>=Table1[Column1]</code>

- Log in to post comments

1 answer

In Microsoft Excel, the INDIRECT worksheet function returns the contents of the specified reference and displays its contents. Its input is a string containing the referenced object (in this case the whole column of a table) and it is not changed while dragging of the formula to other cells.

To make an absolute reference to Column1 of Table1 you can use `INDIRECT("Table1[Column1]")`

- Log in to post comments