There must be a way...

[jrankin]

Hey All:

I've got a quick question that's been bugging me for a while. If I'v
got data arranged by columns, but I've written a formula which I wan
to drag right (i.e. rearrange the calculated data into rows), then ho
can I write a formula so it will just copy over?

The problem is that normally when you copy a formula to the right i
will increment the column letter by one each time while keep ing th
row number referenced in the formula the same. Instead, when I copy i
to the right, I want the column letter to remain the same and the ro
number referenced in the formula to increase by one.

I know I could just copy and transpose the whole data set I'
calculating from; but that seems unnecessary and would screw u
presentation too. I'm sure there must be a way to do this, I'm jus
not sure what it is.

Thanks for all your help in advance!

Cheers,
John R

 

[Leo Heuser]

Hey All:

I've got a quick question that's been bugging me for a while. If I've
got data arranged by columns, but I've written a formula which I want
to drag right (i.e. rearrange the calculated data into rows), then how
can I write a formula so it will just copy over?

The problem is that normally when you copy a formula to the right it
will increment the column letter by one each time while keep ing the
row number referenced in the formula the same. Instead, when I copy it
to the right, I want the column letter to remain the same and the row
number referenced in the formula to increase by one.

I know I could just copy and transpose the whole data set I'm
calculating from; but that seems unnecessary and would screw up
presentation too. I'm sure there must be a way to do this, I'm just
not sure what it is.

Thanks for all your help in advance!

Cheers,
John R.

Hey John

Here's an example.

Data in A2:B8

In C2 you want A2+B2, in D2: A3+B3 etc.

In C2:
=SUM(OFFSET($A$2:$B$2,COLUMN()-COLUMN($C$2),0))

Copy C2 to D2:i2 with the fill handle (the little square in the
lower right corner of the cell)

If the formula doesn't suffice, please post a description of,
what you want to achieve (with formula).