[Solved] Increment Columns

[BAC94]

I have a single column in one sheet that pulls from several columns of another sheet. Each row of the first sheet sums up the column of the third sheet. So I have, for example (Sheet1, Row1, Column1):
=SUM(Sheet3.A1:A30)

But as I go down the column of Sheet1, I want the columns to increment:
=SUM(Sheet3.B1:B30)
=SUM(Sheet3.C1:C30)

But all the different types of fills I've tried increment the row numbers:
=SUM(Sheet3.A2:A31)
=SUM(Sheet3.A3:A32)
etc.

Is there a way to increment column indices?

[acknak]

I don't think this is possible using just the drag-fill, which will always follow the drag direction.

You can do it in two steps (I think).
1) Enter the first formula: =SUM(Sheet3.A1:A30)
2) Drag-fill across to get the other column formulas
3) Select the formulas and convert to absolute references (Shift+F4)
4) Copy the formula cells to the clipboard
5) Edit > Paste Special, Options: Transpose: ON, OK

That should give you the formulas you want, summing the columns while displaying in rows.

The alternative would be to use a formula to choose the ranges to sum, something like: =SUM(OFFSET($Sheet3.$A$1;0;0;30;1))

[BAC94]

Thank you. The first suggestion worked. It was a little finicky, but I got the hang of it.

[wingguy]

I had a spreadsheet the data was entered by rows, vertically, about 150 rows.
I wanted to drag down and increment now only the row number but the column letter also.
The row would increment, but the column letter stayed the same.
Finally fixed this by placing appropiate column letters in a column ( A,B,C,D.... etc ) vertically, way to the side,
out of the way, to correspond to the letter needed in the formula.
Then in the row formula use INDIRECT to fetch the column letter, and the row number increments anyway.
Works like a charm and saved me alot of typing.
Thought I'd pass this on.

[karolus]

I had a spreadsheet the data was entered by rows, vertically, about 150 rows.
I wanted to drag down and increment now only the row number but the column letter also.

Code: Select all

=INDEX($A$1:$AZ$150;ROW(A1);ROW(A1))

pull down!

…now only the row number but the column letter also

??

[Alex1]

Enter =SUM(OFFSET(Sheet3.A$1;0;ROW()-1;30)) in Sheet1.A1, then fill down.