References that move down as formula moves across - and vice versa?
 

Hello, I would like to find the most expedient way to enter a formula that
simply returns the value from another cell... but when I copy it DOWN, the
reference moves RIGHT.

I would like it to work the other way too (if that's possible) so that if i
move it RIGHT the references move DOWN (could the same function work in both
directions)

I am interested in the best worksheet.function way of doing it, but also...

In an ideal world, I'd like to be really easy for everyone in my office to
use. Can I code a function to behave like this? so that it's as simple to
use as count or max?

Thanks

Michelle

References that move down as formula moves across - and vice versa
 

Do you mean the below demonstration..

Col A Col B Col C Col D Col E
1 = = = =
2
3
4
5
6
7

Try the formula in place of = in cell B1 and copy to right as required which
will copy the values from A1,A2, A3 etc;

=INDIRECT("A" & COLUMN(B1)-1)

If this post helps click Yes
---------------
Jacob Skaria


"Michelle" wrote:

Hello, I would like to find the most expedient way to enter a formula that
simply returns the value from another cell... but when I copy it DOWN, the
reference moves RIGHT.

I would like it to work the other way too (if that's possible) so that if i
move it RIGHT the references move DOWN (could the same function work in both
directions)

I am interested in the best worksheet.function way of doing it, but also...

In an ideal world, I'd like to be really easy for everyone in my office to
use. Can I code a function to behave like this? so that it's as simple to
use as count or max?

Thanks

Michelle

References that move down as formula moves across - and vice versa?
 

Probably the easiest to explain to novices might be the Index() function.

The range you're copying *from* is the range you enter first.

Column:
=Index($A1:$A100
=Index($K20:$K50

Row:
=Index(A$1:Z$1
=index(J$41:AM$41

The second part of the formula will *automatically* increment as it's copied
*across*:
=Index($A1:$A100,Columns($A:A))
=index($K20:$K50,Columns($A:A))

OR, for copying *down*:

=Index(A$1:Z$1,Rows($1:1))
=Index(J$41:AM$41,Rows($1:1))

You see here, that the second part *never* changes, dependent on copying
either across or down.

Also, these formulas can be entered *anywhere* on the sheet, without having
to be changed in any way.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Michelle" wrote in message
...
Hello, I would like to find the most expedient way to enter a formula that
simply returns the value from another cell... but when I copy it DOWN, the
reference moves RIGHT.

I would like it to work the other way too (if that's possible) so that if
i move it RIGHT the references move DOWN (could the same function work in
both directions)

I am interested in the best worksheet.function way of doing it, but
also...

In an ideal world, I'd like to be really easy for everyone in my office to
use. Can I code a function to behave like this? so that it's as simple to
use as count or max?

Thanks

Michelle