There must be a way...

[jrankin]

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.

[Leo Heuser]

"jrankin" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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).

--
Best regards
Leo Heuser

Followup to newsgroup only please.