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.
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
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:
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:
copying this cel over C103 to N106 would do the trick.
Now suppose i had put the above data in a 2007 table named
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:
I want to have an absolute reference to
en relative reference to turnover[[january]]. I want something like
(see $ in formula)
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?
Copying the formula to D103 leads to the incorrect:
(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