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

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

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