Microsoft Excel 2007

Use INDIRECT function

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]")

Absolute references in Tables in Excel 2007

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>
Subscribe to Microsoft Excel 2007