O
Ollie4
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)
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)