Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a simple Excel table containing a formula that is referencing columns in another table, and is using the automatic table-range-names (not sure what they're correctly called)

=A1 + SUM(MyOtherTable[ColumnA]) * SUM(AnotherTable[AnotherA])

The problem is that when I copy that formula to the right, the column references all shift right too, so becoming:

=A1 + SUM(MyOtherTable[ColumnA]) * SUM(AnotherTable[AnotherA])
=B1 + SUM(MyOtherTable[ColumnB]) * SUM(AnotherTable[AnotherB])
=C1 + SUM(MyOtherTable[ColumnC]) * SUM(AnotherTable[AnotherC])

How can I stop this? I can't find an equivalent to the dollar-references like $A$A. Note that I do want the normal behaviour for the RC references.

The only alternative I can think of is to created named ranges for the columns in the other tables, although this seems to defeat the object to some extent of the table-ranges.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Instead of dragging, try copy-and-pasting. That works in Excel 2007/2010.

share|improve this answer
    
Oh. Simple. Thanks. –  Cylindric May 16 '12 at 13:59

The way to specify a whole column is with the reference

$A:$A

That will give you a fixed whole-column reference.

Personally, I find Excel much easier to work with when I switch to the R1C1 reference format (Tools/Options/General). Then you can refer to a fixed column as:

C1

Or a relative column as

C[1]
C[-5]

This mode makes it very easy to refer to the current row or column:

RC2

would be the cell on current row, column 2.

share|improve this answer
    
Thanks, I'm aware of using the traditional addressing methods, I was more curious about using the "new way" effectively. Table ranges seem a bit buggy though. I had a named-range set to AnotherTable[AnotherA], but when I use that in a formula, dragging the formula changes the named range –  Cylindric May 16 '12 at 11:02
    
Oh, doh! I didn't spot you were referring to table references - silly me - sorry. The names in tables come from the column headers & I don't think you get any control at all as you've discovered. I think that this is because they originally came from Pivot Tables where you can't arbitrarily drag cells around, only whole data columns, for formulas you have to create pivot table formulas. –  Julian Knight May 16 '12 at 11:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.