XL2007 Table's "Structured References" & Absolute ref

[nbrcrunch]

How to make a table reference absolute?....

I extracted a table from an Oracle db, which XL2007 automatically formats as a Table. No problem there.

However, when I wrote a sumproduct() formula where

=sumproduct((year(Table1[ReqstDate])=$A3)*(month(Table1[ReqstDate])=B$1)*(Table1[ReponseDate]>1))

The formula works fine in the first column. But when i copy it over to columns B thru whatever, it not only increments the cell references for the sumproduct summary but also increments the column heading references from the table.

For example:
Table1, column A is "ReqstDate"
Table1, column B is "ReponseDate"
Table1, column C is "Choice"
Table1, column C is "Choice2"
Table1, column C is "Choice3"


When I drag the sumproduct formula to the right, the 3rd column is changed to

=sumproduct((year(Table1[Choice])=$A3)*(month(Table1[Choice])=B$1)*(Table1[choice2]>1))

 

[xenou]

Hi,
suggested in a thread at stackoverflow is (I think):
1) Copy, rather than drag and 2) hold down the control key while copying...

Microsoft's Using Structured References with Excel Tables was I thought a good link generally It covers this (not very obviously though) under "moving, copying, and filling structured references"

Regards,
Alex.

EDIT: it also appears in the above from MS that you can turn off the "structured referencing" also - I'll have to read all that again though I think.

 

[nbrcrunch]

Alexander: Thank you for that. I tried to find an article on structured references. I'm surprised that one on MS site didn't come up.

I'm a bit disappointed with the way this way implemented. The note about filling, copying, etc, is incorrect. Holding down the Ctrl key does not prevent the columns from being referenced in as in a series. Copying and then pasting to the other cells is the only way I found to do this. What I was worried about is if doing so would also impact the summary grid I designed. Happily, those reference still adjust correctly.

 

[xenou]

Thanks for posting back - no doubt we'll see more threads on table behavior in the future (for better or worse). I've used lists/tables sparingly - the new 2007 tables seem to offer some potential and I should investigate more but I was also surprised at the lack of documentation (I did not find that article searching at MS Online for help on tables...it was only via a link in the thread posted earlier).

Regards,
Alex

 

[Jon Acampora]

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

 

[RandomizerTX]

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

I saw your post, Jon - very helpful. Can you explain how to mix fixed and variable references in table notation? For example, say I have values in the range A1:G1 (1.01, 1.05, 1.02, ..., 1.10). Then in regular range notation, in Cell A2, I'd put
=PRODUCT($A$1:A$1)
and then drag this across Row 2 to have a running product of values in Row 1.