Incrementing Formula by Column Instead of by Row

[Andy]

In one worksheet I have the formula: =SUM(Data!B5:B78) referencing data in a
column on another worksheet. I want to drag the formula down to the next row
so that it will read: =SUM(Data!C5:C78), the row after
=SUM(Data!D578)...etc.

Without dollar signs it increments by row as it should. When I add in dollar
signs like so:
=SUM(Data!B$5:B$78)
and I try to drag it down, it just copies that exact formula to the cell
below it like I typed: =SUM(Data!$B$5:$B$78) even though I didn't.

How do I get Excel to do what I want. I am using Excel 2003. I've searched
everywhere and I can't find an answer.

 

[Glenn]

In one worksheet I have the formula: =SUM(Data!B5:B78) referencing data in a
column on another worksheet. I want to drag the formula down to the next row
so that it will read: =SUM(Data!C5:C78), the row after
=SUM(Data!D578)...etc.

Without dollar signs it increments by row as it should. When I add in dollar
signs like so:
=SUM(Data!B$5:B$78)
and I try to drag it down, it just copies that exact formula to the cell
below it like I typed: =SUM(Data!$B$5:$B$78) even though I didn't.

How do I get Excel to do what I want. I am using Excel 2003. I've searched
everywhere and I can't find an answer.

One way:

=SUM(INDIRECT("Data!R5C"&ROW(2:2)&":R78C"&ROW(2:2),FALSE))