Copying formulas by dragging fill box

[drlogarithm]

I know that dragging a formula down will automatically change the
rownumber within the formula and dragging the formula to the right
will change the column letter. Is there a way to do vice-versa, i.e.,
drag to the right and change the row number?

ex. C2^2 (value of C2 squared)
drag this down and get C3^2
drag this to the right and get D2^2

......looking for a way to drag to the right and go from C2^2 to C3^2
or drag down and go from C2^2 to D2^2 (just opposite of normal - kind
of like me!)

 

[PCLIVE]

This should achieve what you want.

=INDIRECT("C" & COLUMN(B2))^2

HTH,
Paul

 

[drlogarithm]

I can't get your formula to work. Let's say I have the numbers 2, 3,
4 in C2, C3, C4 and I type:

=C2^2

into cell D2, then of course I get 4. Dragging this down autofills
the formula to give =C3^2 in cell D3 which displays as
9 and =C4^2 in cell D4 displayed as 16. I would like to simply (grab
and drag if possible) copy cell D2 which is =C2^2
and move to the right to get =C3^2, =C4^2, etc. Currently, grabbing
and dragging to the right changes the column ref
rather than the row ref.

Where do I enter your formula and why is B2 referenced?

 

[PCLIVE]

That formula works for me when I drag it accross. What should happen when
you drag this formula across is, B2 will change to C2, D2, etc.
If you put this into a cell =COLUMN(B2) you will get 2. That formula
returns the column number for the address that you provide (in this case,
"B2"). The INDIRECT function allows you to maintain the column reference as
you want and then gets the row reference from the COLUMN function. Since
your data starts at row two, that is why we start with column two (B2).
Note the "2" in "B2" has no significance other than to complete a real cell
address.

So that gives us the following formula. Are you sure it doesn't work? Can
you give details?

=INDIRECT("C" & COLUMN(B2))^2

Regards,
Paul

 

[drlogarithm]

This does work for me now, thank you for the explanation - otherwise I
was using B2 in the COLUMN function as a reference for data but I see
it as the row reference now.....B = 2, C = 3, etc.

Thanks again.

 

[PCLIVE]

You're welcome.
Thanks for the feedback.

Paul