Absolute cell reference when using tables

[jakstra]

I am writing some formulas referencing another table in my workbook.

When using structured table references in my formulas like this table_name [header]... compared to actual cell range names like H:H or H1:H1000..

How do I make these structured table references absolute?

When I'm copying these formulas across columns in my worksheet they are moving the table headers relative to the new column position. In other words I want to use structured table references the same way an absolute cell range like this $H:$H would function.

Any advice would be appreciated!

 

[jakstra]

Well if it helps anyone else I think I answered my own question.

Dragging a cell by the handle to 'pull' the formula across multiple columns resulted in the table header reference changing. However, just copying the column with the formulas and pasting them over the range of additional columns resulted in it keeping the original specified table header in the formula.

 

[T. Valko]

When Excel 2007 first came out and I was playing around with the new table "feature" and the structured syntax I quickly realized this is something I would never want to use!

One of the big reasons is just what you've described.

The folks at MS must have figured no one would ever need absolute (or mixed) references using the structured syntax.

I was able to get absolute referencing using the INDIRECT function.

 

[Excelestial]

What do you mean the table headers are moving?

If you want the start and the end column to stay the same, but the rows to change: $H1:$H1

If you want the column AND the Row to stay the same: $H$1:$H$1

If you want the start Column to stay the same, but end rows and end columns to change: $H1:H1

If you want only the start row to stay the same, but all columns and end rows to change: H$1:H1

If you want the start column and the start row to stay the same and the end column and end row to change: $H$1:H1

If you have a named range that you don't want to change: 'Table!$H$1:$H$1000'

 

[jakstra]

What do you mean the table headers are moving?

If you want the start and the end column to stay the same, but the rows to change: $H1:$H1

If you want the column AND the Row to stay the same: $H$1:$H$1

If you want the start Column to stay the same, but end rows and end columns to change: $H1:H1

If you want only the start row to stay the same, but all columns and end rows to change: H$1:H1

If you want the start column and the start row to stay the same and the end column and end row to change: $H$1:H1

If you have a named range that you don't want to change: 'Table!$H$1:$H$1000'

I understand the use of absolute and mixed cell references when referring to cells or ranges in your examples. I was referring to getting an absolute reference when using structured syntax references related to table headers.

 

[jakstra]

When Excel 2007 first came out and I was playing around with the new table "feature" and the structured syntax I quickly realized this is something I would never want to use!

One of the big reasons is just what you've described.

The folks at MS must have figured no one would ever need absolute (or mixed) references using the structured syntax.

I was able to get absolute referencing using the INDIRECT function.

I had to find a way to 'clean up' some lookup formulas in a massive spreadsheet I am using. Rather than taking the easy way to just use the whole column as a look up (ex H:H) which was really dogging some my resources it seemed the easiest way was to utilize tables for the look up ranges and utilize the structured syntax reference to search only relevant data without fearing I may miss something as the lookup tables change sizes as data moves in and out. So far so good..

 

[Jon Acampora]

I posted the following answer here http://www.mrexcel.com/forum/excel-...ables-structured-references-absolute-ref.html

For the columns you want to anchor with an absolute reference you need to duplicate the reference as if it were a range of multiple columns. The following will be anchored to the Choice column.

Table1[[Choice]:[Choice]]

The notation is similar if you want anchor a cell in the same row of the table.

Table1[@[Choice]:[Choice]] will create an absolute reference to the same row in the Choice column if the formula is in Table1.

It's important to note that you must drag the formula across the columns, copy/paste won't work.

I have a post and video on my blog that gives detailed explanation.

Absolute Formula References in Structured Table | Excel Campus

 

[Cobk57]

I am writing some formulas referencing another table in my workbook.

When using structured table references in my formulas like this table_name [header]... compared to actual cell range names like H:H or H1:H1000..

How do I make these structured table references absolute?

When I'm copying these formulas across columns in my worksheet they are moving the table headers relative to the new column position. In other words I want to use structured table references the same way an absolute cell range like this $H:$H would function.

Any advice would be appreciated!

This post is a little old but here is the solution. The Index feature (started around Excel 2010) will do the trick. The Index feature will display whatever is in the cell you have referenced. For example the formula =INDEX(Sheet1!B4:BI225,5,6) will look at the Range at Sheet 1 from B4 to BI225 and return the value of the Column 5 and Row 6.

Updating the Structured data table will not cause the Index formula to drift or move.

Cob

 

[niccoc1603]

The trick Table1[@[Choice]:[Choice]] does not work for me.
However I found out that if you treat the formula as array (Shift+Ctrl+Enter) it maintains the reference if you drag across columns/columns