I've got a (major) problem with the new excel 2007 table
functionality: an absolute reference to a column within the table
doesn't seem to work. This question pas posted before by someone else
but nobody seems to know the answer.

Problem:
In the good ol' days (excel 2003) you coluld use the - $ - sign for an
absolute column or row reference. $A1 would copy down to $A2 but would
copy the right as $A1 still. (see example)

The new 2007 table format gives a much clearer reference but the
reference seems to be relative for columns. If you copy a cell with
calculation with table columns references they behave as relative
column references.

---
Example:

I've twelve columns with months (C to N) and turnover in 100 rows.
Column A is customer (100) an colomn B is Business unit(4).

If I sum in row 102 I only have to write once:
=Sum(C2:C101)
in C102 and copy cell value over all months (C102 to N102) in this
row. This is called a relative reference.

Now, in row 103 to106 I want to sum by the 4 Business units defined
in B103 to B106.
I'll write in C103:
=sumproduct(($B$2:$B$101=$B103)*(C$2:C$101))
copying this cel over C103 to N106 would do the trick.

Now suppose i had put the above data in a 2007 table named turnover,
my sum =Sum(B2:B101) looks like this: =SUM(turnover[[january]]). This
works great copying as it is a relative reference. The sumproduct
would look like this in C103:
sumproduct((turnover[[businessunit]]=$B103)*(turnover[[january]]))

I want to have an absolute reference to turnover[[businessunit]] and
en relative reference to turnover[[january]]. I want something like
(see $ in formula)
=sumproduct((turnover[$[businessunit]]=$B103)*(turnover[[january]]))
but this doesn't work, nor everything else i tried and the internet is
very quit about this topic. Am i the only one have this problem or am
i the only one using tables? Is there - $ - like functionality for a
excel 2007 table column?

P.s.
Copying the formula to D103 leads to the incorrect:
=sumproduct((turnover[[january]]=$B103)*(turnover[[february]]))
(because the january column is next to the businessunit coloumn)

----

If you have the same problem, please post a 'support reply' to keep
this post active

Just got the answer myself in a newer post. Use indirect to make it
absolute
so i.e Table_CBCC_Data[[#This Row],[dateBudget]] becomes
INDIRECT("Table_CBCC_Data[[#This Row],[dateBudget]]")
this will be absolute

see also http://www.cpearson.com/excel/indirect.htm


On 5 feb, 17:20, Ollie4 wrote:
I've got a (major) problem with the new excel 2007 table
functionality: an absolute reference to a column within the table
doesn't seem to work. This question pas posted before by someone else
but nobody seems to know the answer.

Problem:
In the good ol' days (excel 2003) you coluld use the - $ - sign for an
absolute column or row reference. $A1 would copy down to $A2 but would
copy the right as $A1 still. (see example)

The new 2007 table format gives a much clearer reference but the
reference seems to be relative for columns. If you copy a cell with
calculation with table columns references they behave as relative
column references.

---
Example:

I've twelve columns with months (C to N) and turnover in 100 rows.
Column A is customer (100) an colomn B is Business unit(4).

If I sum in row 102 I only have to write once:
=Sum(C2:C101)
in C102 and copy cell value over all months (C102 to N102) in this
row. This is called a relative reference.

Now, in row 103 to106 I want to sum by the 4 Business units defined
in B103 to B106.
I'll write in C103:
=sumproduct(($B$2:$B$101=$B103)*(C$2:C$101))
copying this cel over C103 to N106 would do the trick.

Now suppose i had put the above data in a 2007 table named turnover,
my sum =Sum(B2:B101) looks like this: =SUM(turnover[[january]]). This
works great copying as it is a relative reference. The sumproduct
would look like this in C103:
sumproduct((turnover[[businessunit]]=$B103)*(turnover[[january]]))

I want to have an absolute reference to turnover[[businessunit]] and
en relative reference to turnover[[january]]. I want something like
(see $ in formula)
=sumproduct((turnover[$[businessunit]]=$B103)*(turnover[[january]]))
but this doesn't work, nor everything else i tried and the internet is
very quit about this topic. Am i the only one have this problem or am
i the only one using tables? Is there - $ - like functionality for a
excel 2007 table column?

P.s.
Copying the formula to D103 leads to the incorrect:
=sumproduct((turnover[[january]]=$B103)*(turnover[[february]]))
(because the january column is next to the businessunit coloumn)

----

If you have the same problem, please post a 'support reply' to keep
this post active